Date format causing Run Time 3075 error (1 Viewer)

Chalkie42 · Apr 13, 2013

Hi Folks,
I spent the last seven hours trying to get this to work - me and Google are great friends!

I'm trying to get my bound form to populate a text box with a value (AppointmentDesc) filtered by the ClientID [ComboBox] and the Appointment Date selected in another ComboBox [SessionDate] My code looks like this:

Code:

Me.Text48 = DLookup("AppointmentDesc", "qrySample", "(ClientID = '" & Me.Combo42.Value & "') AND (AppointmentDate = #" & Me.SessionDate.Value & "#)")

The code is in the 'After Update' event of the SessionDate Combo.

I get a runtime error 3075 when the code is executed saying I have a syntax error but I just can't find it.

Can someone please put me out of my misery?

bob fitz · Apr 13, 2013

Try:
Me.Text48 = DLookup("AppointmentDesc", "qrySample", "ClientID = " & Me.Combo42 & " AND AppointmentDate = #" & Me.SessionDate & "#")

Chalkie42 · Apr 13, 2013

Thanks for the quick reply.

The control where you removed the single quote marks is not numeric. It is alphanumeric e.g. SW/13/001. Maybe I should have mentioned that.

bob fitz · Apr 13, 2013

Chalkie42 said:
Thanks for the quick reply.

The control where you removed the single quote marks is not numeric. It is alphanumeric e.g. SW/13/001. Maybe I should have mentioned that.

OK. Is the data type of field ClientID "Number". You will need to look a its table in design view to check this.

Have you tried:
Me.Text48 = DLookup("AppointmentDesc", "qrySample", "ClientID = '" & Me.Combo42 & "' AND AppointmentDate = #" & Me.SessionDate & "#")

Chalkie42 · Apr 13, 2013

The Client ID is set to Text in the table. As far as I can see the code you suggested is the same as what I posted without the brackets that are only there for clarity - they don't do anything and removal of the .Value extension.

Anyhoo - did that and no dice! Same: Syntax error in date in query expression 'ClientID = " AND AppointmentDate = #'.

bob fitz · Apr 13, 2013

Can you post a copy of the db in A2003 mdb format.

Chalkie42 · Apr 13, 2013

Aplologies. I am working in A2010 which refuses to save in A2003 because I am using features....etc.

bob fitz · Apr 13, 2013

How about:

Code:

If Me.Dirty Then
  Me.Dirty = False
End If
Me.Text48 = DLookup("AppointmentDesc", "qrySample", "ClientID = '" & Me.Combo42 & "' AND AppointmentDate = #" & Me.SessionDate & "#")

Chalkie42 · Apr 13, 2013

Thanks for that but I still get the same error.

bob fitz · Apr 14, 2013

The code below works.

Code:

Me.Text48 = DLookup("AppointmentDesc", "qrySample", "ClientID = '" & Me.Combo42 & "' AND AppointmentDate = #" & Format(Me.SessionDate, "mm/dd/yyyy") & "#")

If this does not work for you, then check the name property of the form controls (SessionDate and Combo42). Also check the names of the fields in qrySample (AppointmentDesc, ClientID and AppointmentDate)

Chalkie42 · Apr 14, 2013

I seem to have cracked this problem. I had all my controls bound to the query - undid that. then I changed the syntax to this:

Code:

Me.SessionType = DLookup("[SessionType]", "[qryMissingNotes]", "ClientID = '" & Me.ClientID & "' AND DateValue(SessionDate) = #" & DateValue(Me.SessionDate) & "#")

All works fine now.

Many thanks for your kind assistance.

Date format causing Run Time 3075 error (1 Viewer)

Chalkie42

Registered User.

bob fitz

AWF VIP

Chalkie42

Registered User.

bob fitz

AWF VIP

Chalkie42

Registered User.

bob fitz

AWF VIP

Chalkie42

Registered User.

bob fitz

AWF VIP

Chalkie42

Registered User.

bob fitz

AWF VIP

Chalkie42

Registered User.

Similar threads

Users who are viewing this thread