Comparison Query

[mkc80]

I have a problem with my query. It was working fine until I added the 'Type Of Bill' Criteria.


Site and Patient are character columns, Admit Date is of date format and Type Of Bill is a numeric column.

I get the following error now: Application defined or object defined error.


If IsNull(DMax("Line_Number", "DummyDetail", "[Site]= '" & Parent!Site.Value & "' AND [Patient] = '" & Parent!Patient.Value & "' AND [Type_of_Bill] = " & Parent!Type_of_Bill.Value & " AND [Admit_Date] = #" & Parent!Admit_Date.Value & "#"))
Then Line_Number.Value = 1

(these statements are in the beforeinsert event of the form, which is bound to the DummyDetail table)

Line Number is a column in DummyDetail table, which needs to be reset to 1 if the bill type doesn't already exist in the table (for that particular site, patient and admit date)

Please advise.

[John_G]

Bill type - is it [Type of Bill] (with spaces) or [Type_of_Bill] (with underscores) in the table Dummydetail and the Parent form? That's the only thing I can think of that might be causing the error.

John

[mkc80]

It is Type of Bill with spaces.

[John_G]

OK, there's your problem then. In your code, you need to refer to it as [Type of Bill], with spaces, not underscores, and in square brackets. Your statement then becomes:

If IsNull(DMax("Line_Number", "DummyDetail", "[Site]= '" & Parent!Site.Value & "' AND [Patient] = '" & Parent!Patient.Value & "' AND [Type of Bill] = " & Parent![Type of Bill].Value & " AND [Admit_Date] = #" & Parent!Admit_Date.Value & "#"))
Then Line_Number.Value = 1


John