Query returning numbers instead of names Results

Hi all

I have a simple query that is set to return the last data dependant on a date, the thing is some of my fields that have names in them return with a number not the name must be a setting wrong somewhere but cant find it.

Any help apprieciated.


Hello everyone,

Hope someone can help.
I build a combo box in a form that selects customers names from the customers table. On the form, no problems. However, if I look in the table, the customer's id is stored in the field, not the name.
Of course, that has an impact on the queries and reports extracted from that table in that all show customer id instead of name.

Your help would be much appreciated, thank you.

Hi All:

I have a report that calculates equipment totals for the month and is grouped on Equipment Type (Text) and sorted by Equipment Name (Text). The report is based on a query that returns the results exactly as I want them; but for some reason, the report is showing the Equipment Type fields as a Number instead of Text.

My first thought was that the Format of the Text field was wrong, but when I try to change it -- it is currently blank -- there are no drop-down options...

Then I thought that maybe the report is picking up on the Equipment Type ID (Autonumber & PK in tblEquipmentTypes), but the numbers that are showing in the report don't correspond with the Equipment Types that are showing up in the report (which, strangely, start at 0).

Any thoughts why this would be happening? I just tried to re-create the report with the Wizard as a quick test and the problem appeared again. Why might this be occurring somewhere between the "perfect" query and the "flawed" report?

Many Thanks!!


This SQL statement is suppose to be selecting some information from a form where some text boxes are attached to fields in a table and some textboxes that don't have a datasource but do have calculated data.

	SELECT Clients.Envelope, Clients.Attention, Clients.Address, Clients.Suburb, Clients.Postcode, Clients.State,
Forms!ITWV!ClientCode.Value AS ["Client Code"], Forms!ITWV!GivenNames.Value AS ["Given Names"], Forms!ITWV!Text38.Value AS
["Negative Gearing"], Forms!ITWV!RevisedTaxRate.Value AS ["Revised Tax Rate"]
FROM Individuals, Clients
WHERE ((([Forms]![ITWV]![IndividualID])=[Individuals].[IndividualID]) AND

It's returning:

	Envelope	Attention	Address	Suburb	Postcode	State	"""Client Code"""	"""Given Names"""	"""Negative Gearing"""	"""Revised Tax
Mrs N Aradajlah	Nadine	7 Endenby Close	HINNBROK	2198	NSW          AWA001                   Nadine                      
啕ꕵ骪䂁                    쳍㹌

The query is returning the correct data in all the fields EXCEPT for the 'Negative Gearing' and 'Revised Tax' textboxes on the ITWV form. Instead of numbers it is returning '啕' characters.

Why is it returning 啕啕 instead of 0.2 and 啕啕啕啕 instead of 563.333323399226?


I've read this thread http://www.accessforums.net/queries/...eys-18435.html and the associated external link http://access.mvps.org/access/lookupfields.htm several times - I'm having essentially the same issue. I'm not sure if I'm reading it wrong, but it looks like a bunch of statements without a solution. How am I supposed to deal with this situation?

I have a database that for all intents has four tables

1. key information (standard primary key, key number is alphanumeric and stored as a text object).
2. signout information - long term
3. signout information - short term
4. user information

I have two queries set up that return all keys currently signed out. These work fine, and return the correct results.

What I need to do next is create a query that puts together both the long term and short term keys signed out, and then filters so that it only includes results from the named individual input (the point being for us to query someone who leaves so we can get their keys back). I've created a union query from the two queries that return all keys signed out, but instead of returning the key number, it returns the primary key for the key number. It actually works fine when I create the query, but when I save, close and re-run it, it starts returning the primary key.

My SQL for this query looks like this -

SELECT Who, [Key Number]
FROM qry_allcurrentshorttermout
SELECT Who, [Key Number]
FROM qry_allcurrentlongtermsignout;

I don't understand why my long and short term queries on their own are returning the correct values for key numbers, but another query based on them doesn't.

Can anyone help me see the light?

I have an Access database report based on criteria entered by the user.
The problem I have may not necessarily be MS Access related.
When I run the report on my machine the report opens fine with the correct information.
When someone else tries to run the report on their machine the report does not return the correct information.
The dates in the report are not within the date range specified by the user.

The following lines of code opens the report
the variables 'stdate' and 'eddate' are pulled from controls on a form

DoCmd.OpenReport stDocName, acPreview, , "last_del_date between #" & stdate & "# AND #" & eddate & "# and territory_code like '" & SelTerr & "' AND maingrp_code like '" & SelMain & "' AND prodgrp_code like '" & SelProd & "' and ware_code like '" & SelWare & "' and ord_ind like '" & SelOrd & "' and status_ind like '" & SelStat & "' and part_code like '" & SelPart & "'"

Heres the catch, When I log onto the other users machine using my logon the report works fine.
If the other user tries to log onto my machine using their logon the report fails.

I have checked the regional settings on both computers however this should not effect the query as Access converts any date to a serial number regardless of the format.

The filter in the open report command has the dates enclosed in # and the controls on the form that I have assigned the date variables to are formatted as type 'Date'.

I looked at the .inf file that is accessed when a user logges onto their machine as I thought this might be the cause.

When I overwrote another users file with my .inf file it seemed to work for them until they log on to their computer again.

The .inf file seems to set application settings at a user level when you log onto your machine. I don't think the settings are stored in the .inf file itself but instead in other files that the .inf file calls

We run Windows 98 and MS Access 97

Their is also an identical .inf file stored in the users Home directory on the network.


This is an employee database that will be used by management to keep track of tasks, backlogs, etc.

I have a QBF form set up to query based on user input.

The form queries many tables and turns the data to a report.

My problem is when the form displays, it lists all the fields from all the tables. I do not want this. I just need individual records listed from each table in the report. The individual field names of each table do not need to de displayed, the data must appear under my "generic" fileds listed in * below.

I have many tables set up like the following: (defined by task) i.e., each table is for a specific task

ID: autonumber
tblatr_ID: text (this field is automatically filled with the name of the task when the user keys)
tblatr_date: Date/Time
tblatr_user: Text
tblatr_num_req_rec: number
tblatr_num_req_pro: number
tblatr_time: number
tblatr_oldest_date: date/time

The query by form allows the user to do a parameter query. The report I created picks up the query (QBF) info.

I need the report(s) field(s) to be displayed dynamically like this:

*user - date - # of requests processed - # of requests received - time - ID (task)

is there any way to have records from all searched tables display under these field headings in the report? The records need to be returned one after another, as in a spreadsheet, but they will be from different tables.

Please help! I have a headache.......

If anyone needs me to post my code, etc. let me know!

below is an example of my QBF form (work in progress)

Option Compare Database
Option Explicit

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

' Note the single quotation marks surrounding the text fields [Ship
' Country] and [CustomerID].
' Note that there are no type-casting characters surrounding the
' numeric field [EmployeeID].

where = Null
where = where & " AND [tblce_user]= '" + Me![UserName] + "'"

'NOTE: In Microsoft Access, when you use the plus sign (+) in an
'expression in which you are concatenating a variable of the numeric
'data type, you must use parenthesis around the syntax, as in the
'following example:
' where = where & (" AND [EmployeeID]= " + Me![Employee Id])
'You must also use a conversion function to make sure that the proper
'conversion (to either NULL or String) takes place.

' The following section evaluates the ShipCity criteria you enter.
' If the first or last character of the criteria is the wildcard
' character (*), then the function uses the "LIKE" operator in the
' SQL statement instead of "=". Also note the single quotation
' marks surrounding the text field [ShipCity].
If Left(Me![UserName], 1) = "*" Or Right(Me![UserName], 1) = "*" Then
where = where & " AND [tblce_user] like '" + Me![UserName] + "'"
where = where & " AND [tblce_user] = '" + Me![UserName] + "'"
End If

' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![UserEndDate]) Then
where = where & " AND [tblce_date] between #" + _
Me![UserStartDate] + "# AND #" & Me![UserEndDate] & "#"
where = where & " AND [tblce_date] >= #" + Me![UserStartDate] _
+ " #"
End If

If (Me!task) = "cycle ends" Then

GoTo exit_tblce
End If
If (Me!task) = "all" Then
GoTo exit_all
End If

Exit Sub

' Remove the following MsgBox line if you do not want to display the
' SQL statement.
' NOTE: The Mid function is used in the following MsgBox function to
' remove the word AND that follows the first Where clause. If you do
' not use the Mid function, the SQL statement contains the word AND
' at the beginning of the WHERE clause, for example:
' Select * from Orders where AND [CustomerID] = 'CACTU'
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblce " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "test report", acViewPreview

Exit Sub

Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblce,[tbldts],[tbl3541],[tblatr],[tblatr1],[tblatu]" & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"

Exit Sub
MsgBox "Select * from tblce " & (" where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblce " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "test report", acViewPreview

End Sub

Private Sub Detail_Click()

End Sub

Hi i've got a couple of problems,

is it possible to reference a sub query in its parent report,

I have severy queries in a report and i need to total the result of these queries (btw the queries count and only return a single number) was wondering how I can reference the sub query


the same report above, the criteria for the sub queries is generated by user input to a form, (Site Name, Start Date, End Date)

I need the title of the form (which is "site name" which is specified in the query form and passed into the reports openArgs) at the top of the report but because the Site name is generated by the lookup wizard it returns the ID instead of the site name e.g. "3" instead of "Lake District" is there anyway i can retrieve the name instead?

any help appreciated


I have a report based on a query where in the query the user inputs a month from 1 - 12. How do I make the report say the name of the month instead of the inputted number?

The field in the query is constructed like this:

Field Month: Month([tbl_2_CustomerOrder.OrderDate])

Criteria [Input month (1-12)]

I've tried using =Format$([Month],"mmmm") but it always returns "January". What am I doing wrong?


I have a pass through query that executes a stored procedure that will return results that I use on an MS access report. I have created the pass through query like I do for all of my forms and instead of putting it in the record source property I put code in the load event to populate the record source. This works fine for a form....but it doesn't work for a report. If I put the name of the pass through query into the report record source property it runs, but for some reason it doesn't update if I run the report again with different criteria.

How can I have a pass through query as the record source for a report and have it update everytime I run the report again? I checked the pass through query and it just holds the first parameter I ran the report with. If I remove the pass through query from the record source properties of the report it doesn't run at all. I set a breakpoint and its like it doesn't even get to the report load event, but it will open the report it will just have nothing in it.

Private Sub Report_Load()
Dim rs As DAO.Recordset
Dim db As Database
Dim rstEncounterDetails As DAO.Recordset
Dim strEncounterEmailSQLString As String

'Set the Encounber number variable equal to the first (and only in this case) argument passed in from the measure worklist.
strProvTbleID = [Forms]![frm_SendVarianceNotice]![ProvTbleID]

'Build SQL string to pass into sp_GetWorklistAMI based on user input parameters.
strEncounterEmailSQLString = "EXEC sp_GenerateVarianceEmailMainReport " & strProvTbleID
' Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("sp_GenerateVarianceEmailMainReport")

'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strEncounterEmailSQLString

'Allow the stored procedure to return a result set to be bound.
qdf.ReturnsRecords = True

'Open Recordset object as a query def.
Set rstEncounterDetails = qdf.OpenRecordset()

If rstEncounterDetails.EOF Then
MsgBox ("There are no variances attached to this encounter")
Exit Sub
End If
'Move to the first record of the record set.
'Bound the current form that is loading to the recordset returned by the stored procedure containing only user's selection.
Set Me.Recordset = rstEncounterDetails

End Sub

So this may seem like a bit of an odd duck question but I've about 20 or so fields in a certain query that all require a certain set of calculations (all of which are related).

What I'd like to is extract the field name of one of my arguments as a string and use it as a criterion for how the function operates instead of creation 20 different functions that all do nearly the same thing.

I'm not completely sure that access could even /do/ this but thought I'd try.

Ex: Func([Start_After_Days])

	Public Function Func(Arg1 As Integer) as Date

Dim strArgName As string


Where 'msgbox strArgName' would return Start_After_Days

Any thoughts? I can't use .Name (at least I don't think I can--might be misusing it, I guess). I know I could recordset it using the same query this function will be used in as a sql statement and retrieve it that way, but that seems like the long way around and frankly a bit resource expensive (as the number of calculations that need to be done grow quite large near the end of this set of functions).

Thanks in advance,
~Chad (Vb wannabee)

Edit: Just ran across something mentioning Get Property() Anyone know if this is a false lead?

I got a bit of a problem with the search function using queries. Now I have a unbound form to display a query results, and a unbound text box where you enter the text in to search - this auto updates the query of the results - the working sample was posted by a user here ref "cool serach tool" - and the code:

Quote: Option Compare Database
Option Explicit

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
Me.Search2 = ""

Exit Sub

MsgBox Err.Description
Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()

Me.RecordsetClone.FindFirst "[Name] = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub

Private Sub Search_Change()
Dim vSearchString As String

vSearchString = Search.Text
Search2.Value = vSearchString

End Sub But here is the problem, I have tweaked it to queries all current live records using the yes/no funtion by adding True in the critrea of new Coulum that extracts only the up the current "live data" that has yes on it, When i type in the text field the code I am looking at its returning the correct result in the unbound form, but when I click on it to show the results in th form it is showing the information for the delisted ingredient instead of the current live ingredent!! - both sharing the same ingredient code, but different ID codes. The Primary key (ID Code) is done by autonumber - what is going wrong and how do I stop it?

My Coding on the form:

Quote: Public Sub SendMail()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer

strSubject = "Latest Job Outcomes"
strEmailAddress = "[Mail Addresses Go Here]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")

intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
, "[ysnSentByMailToStaff]=0")

If intCount = 0 Then
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
, vbInformation, "System Information"
Exit Sub

Do Until rst.EOF

strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
& " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
& " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
& "Below are the details that have been submitted by the student:" _
& Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
& "Graham"

DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False

Set rst = Nothing
Set dbs = Nothing

'Run update to update the sent mail check box
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblJobOutcomes SET tblJobOutcomes.ysnSentByMailToStaff = -1"
WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
DoCmd.SetWarnings True
MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
End If
End Sub

Option Compare Database
Option Explicit

'ghudson 11/27/2002

'For those of us not using Access XP, we have a challenge to prevent
'our users from advancing to another record if they do not use the controls
'we want them to use or to prevent them from bypassing our validation
'procedures too ensure the current record is okay to be saved.

'The trick in my form is the value in the tbProperSave text box. The user will
'not be able to advance to another record using their scrolling mouse wheel nor
'will they be able to use the Page Up or Page Down keys nor will they be able
'to use the Shift-Enter keys to save the record. The user is forced to save
'or undo the modified record using my custom save or undo buttons before
'they can advance to another record or before they can close the form.

Private Sub bQuit_Click()
On Error GoTo Err_bQuit_Click


'Prompts the user to save the current record if it needs to be saved.
If Me.Dirty Then
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not close this form until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
'DoCmd.OpenForm "fMainMenu"
DoCmd.Close acForm, Me.Name
End If

Exit Sub

MsgBox Err.Number, Err.Description
Resume Exit_bQuit_Click

End Sub

Private Sub bSave_Click()
On Error GoTo Err_bSave_Click


If IsNull(FirstName) Then
MsgBox "All required fields must be completed before you can save a record.", vbCritical, "Invalid Save"
Exit Sub
End If

Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & " Yes: Saves Changes" & vbCrLf & " No: Does NOT Save Changes" & vbCrLf & " Cancel: Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Save Current Record?")
Case vbYes: 'Save the changes
Me.tbProperSave.Value = "Yes"
DoCmd.RunCommand acCmdSaveRecord

Case vbNo: 'Do not save or undo
'Do nothing

Case vbCancel: 'Undo the changes
DoCmd.RunCommand acCmdUndo
Me.tbProperSave.Value = "No"

Case Else: 'Default case to trap any errors
'Do nothing

End Select

Exit Sub

If Err = 2046 Then 'The command or action Undo is not available now
Exit Sub
MsgBox Err.Number, Err.Description
Resume Exit_bSave_Click
End If

End Sub

Private Sub bUndo_Click()
On Error GoTo Err_bUndo_Click


'Resets the record if it has been modified by the user.
If Me.Dirty Then
DoCmd.RunCommand acCmdUndo
Me.tbProperSave.Value = "No"
MsgBox "There were no modifications made to the current record.", vbInformation, "Invalid Undo"
End If

Exit Sub

MsgBox Err.Number, Err.Description
Resume Exit_bUndo_Click

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate


If Me.tbProperSave.Value = "No" Then
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
Exit Sub
End If

Exit Sub

If Err = 3020 Then 'Update or CancelUpdate without AddNew or Edit
Exit Sub
MsgBox Err.Number, Err.Description
Resume Exit_Form_BeforeUpdate
End If

End Sub

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Me.tbProperSave.Value = "No"

Exit Sub

MsgBox Err.Number, Err.Description
Resume Exit_Form_Current

End Sub

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
Me.Search2 = ""

Exit Sub

MsgBox Err.Description
Resume Exit_ClearIt_Click

End Sub

Private Sub QuickSearch_AfterUpdate()

Me.RecordsetClone.FindFirst "
 = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub

Private Sub QuickSearch_Click()

End Sub

Private Sub Search_Change()
Dim vSearchString As String

vSearchString = Search.Text
Search2.Value = vSearchString

End Sub

Private Sub Search_Click()

End Sub Thanks

Hello everyone,

I am trying to create a report in which it is populated by only the current month's population of juvenile detainee's. I need to have a field in this report that basically numbers each juvenile. EG:

Line| Last Name| Middle Initial| FirstName
1 | Doe | John | John

2 | Doe | Jane | Jane

This is what I've got going so far.

On open of the report an append query appends only the records for the current month to a temporary table named "Monthly Pop" then the report itself is based off this table. (the reports record source IS this table)

On the table itself I have an autonumber field which I thought would provide me with a good counter; however, it only works correctly on the first run. Let me go on with how my system works so I can fully explain why it does not work.

After the user closes the report I have a delete query which deletes all the records from the temporary table that are in the current month.

The problem I am getting is once the report is open again and hence the append query is also run, the auto number does not start at 1. It starts based on the last number of the previous appeneded records. For example if the first run of the append query returned 3 records and then the delete query did its work on exit of the report, the next append query would start at 4 instead of 1. I am not sure what to do.

I tried adding =Count([Monthly Population].[random field to count]) to my reports ID column but it fills in the total number of records instead of actually counting the records.

I have created a database from scratch and am happy with how my tables and data entry form are working. I am trying to create a query that will allow me to search for specific data, but instead of returning the text that appears in the field of the form, it is coming back with the auto number field. For example. I have drop down lists to select employee's name from another static table. When i query and that field comes up with a number from the auto number field rather than the employee's name. Any help you can give me would be greatly appreciated. Thanks!

My union query is returning a numeric value instead of a city name. The original query does not have this issues. Any ideas??

Extremely new to queries and still don't understand quite how they work, but I have a query that is almost working the way I would like.

SELECT StudentInformation.[790ID], StudentInformation.LastName, StudentInformation.FirstName, StudentInformation.DegreeType, CourseTaken.CourseType, CourseTaken.CourseStatus, CourseTaken.DepartmentID, CourseTaken.Term, CourseTaken.Year, CourseTaken.Units, CourseTaken.Grade, CourseTaken.Transfer, UnitTotal.TotalCredits
FROM (StudentInformation INNER JOIN CourseTaken ON StudentInformation.[790ID] = CourseTaken.[790ID]) INNER JOIN UnitTotal ON StudentInformation.[790ID] = UnitTotal.[790ID]
WHERE (((StudentInformation.[790ID])="790-61-5935") AND ((CourseTaken.CourseStatus)="completed"))
ORDER BY CourseTaken.CourseType;

It is pulling the information I need, but the DepartmentID is just displaying the auto number (10 or 11) instead of "Department ID = PB 510 CName"
The department ID field pulls from this:
SELECT Courses.[CourseID], [Courses].[DepartmentID], [Courses].[CourseNo], Courses.[CourseName] FROM Courses ORDER BY [DepartmentID], [CourseNo], [CourseName];
How do I get it to display the deptid (PB) c# (510) and name (CName)?

I tried adding the Courses table and pulling the three fields but it does not return any information when I did that. then i tried adding the courses table and linking the student information table deptID field to the three fields but again nothing returned.
any help would be greatly appreciated. I have added the return the way the top is wrote.gradquery.jpg

Im trying to use the Dlookup value on a form just to display some info for the user.

im using
=DLookUp("Premises_Name","[Meeting_Agenda]","Meeting_Agenda_Number = " & [Meeting_Agenda_Number])

but instead of displaying the premises_name = its actually displaying the premises number (i.e. 16 instead of the name).

in the Meeting_Agenda table, there is a drop down box that has all the premises names, and these come up with the names.
they are referenced to a Premises table via premises_number.

i would have thought that if the value stored in the meeting_agenda table would have been the actual name, rather than the premises number, so that all i had to do was reference to that.

how can i make it so that it displays the prem. name rather than the number?

I have searched other threads and cannot find one that helps solve my problem. So, I hope someone can help.

I have a query which runs ok until it is filtered. I have tried using criteria to filter the info I need but this returns an 'Enter Parameter Value' message. Running the query without using criteria works okay but using a filter in the report itself returns a 'Data Mismatch' message.

The report filters fine on fields until it gets to one that has been changed from text to number. It also includes an Nz formula. The formula in the is field is: CLng(Nz([order_no],0)). Other fields related to this also appear to be affected.

I need the CLng formula as the field is needed to calculate other fields using a zero instead of a null which would otherwise cause an error. I was given the formula on this forum yesterday, (thanks Bob Larson and AlanJ10), which was great in solving the other issue but is this is what is affecting filtering, (ie. a confusion between text and number?!?!) I have also tried converting the result of the calculation I want to filter back into text using CStr but this does not make any difference.

I have checked the field names and also renamed fields that were 'Expr' (as in default names for calculated fields), and cannot see any problems.


This is a many table of Chart numbers and labs. Each lab has a lab date and a lab value. There are 65 different labs and some chart numbers may have multiples of each, while others may only have one each of 6 or 7 labs.

What I’m trying to get are the 3 most recent dates of each lab for each chart. I’ve tried several different things, but nothing is quite right. The one below seemed to be the closest, but it wasn’t really all that close. I got the same three lab names for all chart numbers. It returned all of the labs of those three, instead of the top 3 of all.

I also tried the top 3 LabDates, but then I only received 3 dates for multiple labs and charts. When I tried doing a combination of the 2 I would get various errors. One tantalizing error had to do with the EXISTS reserved word, but I couldn’t get it to work.

Any help is appreciated. There is a “one” table of patients that goes with the “many” side of this tblLabs table that is not incorporated in this query. [ChartNumber] is the join field.

SELECT tblLab.ChartNumber, tblLab.ItemName AS LabName, tblLab.LabDate, tblLab.LabValue
FROM tblLab
WHERE (((tblLab.ItemName) In (SELECT
Top 3 [ItemName]
tblLab ORDER BY [Chartnumber], [ItemName], [LabDate] ASC)))
ORDER BY tblLab.ChartNumber, tblLab.ItemName, tblLab.LabDate DESC;

Hi, I have a signle table with a large number of feilds (project ref, name dates etc... and a large number of skills). the skill feilds hold a number (number of days) assigned to each skill. If no values are entered no skill for that particular project is needed.

Many of these feilds remain null, but many have a value (but not for all rows).

I want a query to retrieve all results excluding a feild with no values at all (all null).


Project code:_Skill1__skill2__skill3__skill4__Skill5__Skil l6
AA00001______ 1__________ 4

Now if I use a query to select all rows and use the criteria Is not null (on each individual feild) it returns nothing at all, due to the fact that each skill has a null. all i want excluding from the example above are feilds 'skill2 and skill6'. Would this have to be a manual operation to find each feild with no vaules and leave them out of my query? I would much prefere a query to exclude each feild with 0 values for every project.

Keeping in mind I have over 30 skills in my table.

I also tried adding a 0 instead of a null and trying >0 as the criteria, but returns no results much like the Is not null.

P.s I also have another relational DB (a test) keeping the skills in a seperate table (using the project code for Primary/Foreign key), I cannot find a method for either way...

many thanks.

Not finding an answer? Try a Google search.