Restrict Form RecordSource, based on Date selected in combo box

[Leonard Priestley]

I have sometimes restricted the recordsource of a table, using code like
this:
Me.RecordSource = "SELECT Table1.ID, Table1.Text, .... From Table1
WHERE Table1.Text = ' " & cboTextSearch & " ' "
This has worked well with text and numbers (for numbers I would put:
WHERE Table1.Number = " & cboNumberSearch )

However, I would now like to do the same thing with dates, and I just can't
seem to find the correct syntax. Can anyone help please.

Leonard Priestley

 

[PC Datasheet]

WHERE Table1.MyDateField= #" & cboDateSearch & "#"

Note: Never use "Date" as the name of a field. Date is a reserved word in
Access.

 

[Leonard Priestley]

Well, I have tried that and have had no luck. The reason is, apparently,
that I live in a country which uses the European format of DD/MM/YY.

I found a website which suggested using the following syntax:

....WHERE [Table1].[Date] = # " & Format(cboSearch, "YYYY/MM/DD") & "#"

This works very well. The only problem is that I work for a group of large
public hospitals, and my chances of convincing all the staff to go to their
control panel and set YYYY/MM/DD instead of DD/MM/YYYY is nil. Varying the
format to DD/MM/YYYY in the code above produces variable results: sometimes
it works, sometimes not. I don't know why. I am beginning to wonder if I
am going to have to convert my date to Julian Day style and back again.

Please, someone, tell me that Microsoft thought about how people in other
countries would want to code with dates. There has to be a reliable way,
surely.

Leonard Priestley

 

[Leonard Priestley]

Whoa, hold it!

I have just found that if you code using YYYY/MM/DD, you don't have to
change your date settings to correspond in the control panel. You can
still input and output dates in the DD/MM/YY format. I don't quite follow
the logic there, but I'm not about to complain.

Hallelujah

Leonard

Well, I have tried that and have had no luck. The reason is, apparently,
that I live in a country which uses the European format of DD/MM/YY.

I found a website which suggested using the following syntax:

...WHERE [Table1].[Date] = # " & Format(cboSearch, "YYYY/MM/DD") & "#"

This works very well. The only problem is that I work for a group of large
public hospitals, and my chances of convincing all the staff to go to their
control panel and set YYYY/MM/DD instead of DD/MM/YYYY is nil. Varying the
format to DD/MM/YYYY in the code above produces variable results: sometimes
it works, sometimes not. I don't know why. I am beginning to wonder if I
am going to have to convert my date to Julian Day style and back again.

Please, someone, tell me that Microsoft thought about how people in other
countries would want to code with dates. There has to be a reliable way,
surely.

Leonard Priestley

WHERE Table1.MyDateField= #" & cboDateSearch & "#"

Note: Never use "Date" as the name of a field. Date is a reserved word in
Access.