Access 2007: Correct Data Type

[netchie]

Hi,



I have a field named "Name" in tblError that can be either text or number. When I created a report to pull out specific Name, the report only shows all names with "text" data type and it won't show the ones that are in Numeric data type. Is there a way I can show either text or numeric? How can I do this?

[boblarson]

First off, the use of NAME as the name of a field (or object, for that matter) is a very, very bad idea. In fact, it can come back to haunt you at the worst times. The reason? Every object in Access has a NAME property. And you actually can use this property in square brackets at times on reports which, if you have a field named NAME it can get confused as to what you want.

So, your field is stored as text I assume (in order to accomodate both text and numbers), is that correct? What is the SQL of the report? Do you have a control on your report that is also named the same as the field (NAME - I ask because that is the default that Access does when you create a form or report with the wizard or on drag and drop)? If so, change it to txtName.

[netchie]

Hi boblarson,

Wow! Thanks for the advice. I just changed it.

Here is the SQL after I clicked "Run Report":


Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim stDocName As String
stDocName = "rSponsorSpecificStudy"
DoCmd.OpenReport stDocName, acPreview
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

[netchie]

.. and this is what I have on my query builder:

SELECT tCARs.Sourceidentifier
FROM tCARs
WHERE (((tCARs.Sourceidentifier) Is Not Null))
ORDER BY tCARs.Sourceidentifier;

[boblarson]

Here is the SQL after I clicked "Run Report":

Sorry, that is the VBA. The SQL is the query that the report is based on and if the report is based on a saved query you would go into that query in design view and then select VIEW > SQL VIEW and then copy and paste that. If you have just selected the table for the report's record source, I would suggest either using a saved query or using a SQL Statement in the record source directly:

SELECT TableNameHere.* FROM TableNameHere ORDER BY FieldNamehere

(that is a very simplified example and not intended to be something you can just use in your report)

[boblarson]

.. and this is what I have on my query builder:

SELECT tCARs.Sourceidentifier
FROM tCARs
WHERE (((tCARs.Sourceidentifier) Is Not Null))
ORDER BY tCARs.Sourceidentifier;

You posted while I was still typing so sorry about the in-depth describing of the query part.

It looks like you have only one field being selected. Is that the entire thing for your report? If so, I would try this instead (since it is text):

SELECT tCARs.Sourceidentifier
FROM tCARs
WHERE (((Len(tCARs.Sourceidentifier & "") > 0))
ORDER BY tCARs.Sourceidentifier;

[netchie]

I'm getting syntax error. It says missing operator in query expression '(((Len(tCARS.Sourceidentifier &"")>0))ORDER BY tCARS.Sourceidentifier;'.

[boblarson]

Sorry, I hate the parens that Access puts around stuff. Since you don't have table joins to mess with, this should work:

SELECT tCARs.Sourceidentifier
FROM tCARs
WHERE Len(tCARs.Sourceidentifier & "") > 0
ORDER BY tCARs.Sourceidentifier;

[netchie]

Some are showing data and some are blank

[boblarson]

Sorry, can you post a copy of your database here so we can take a look to see what is happening? (make sure not to post personally identifiable information or sensitive information - if it has that type of data, either scramble it or put in bogus data for that particular information).