syntax in the criteria of the openform

[alicejwz]

What is the correct syntax for where criteria in the openform method?
I would like to compare the shipped date in the form based on a table with the input date. The error occurred in the syntax in input date because I tested with an actual date and is working.
I tried various ways but still can't figure out what is the syntax for the date.

This is what I have
DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='& # bdt # &'"

Thanks for your help!

 

[FoFa]

Given bdt is a proper date format
DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] = #" & bdt & "#"

 

[alicejwz]

syntax in openform

no, it is still not working.
It works with an actual date in this format
DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='7/8/2003'"
any other suggestions?

Thanks

 

[dcx693]

It looks like your dates are being stored as strings, which is not optimal, but if you can't change that, try this syntax:

DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='" & bdt & "'"

 

[alicejwz]

type mismatch

These all work
DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] = " & " ' " & bdt & " ' "
DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='7/8/2003'"
DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] <= " & " ' " & edt & " ' "


but when I combined the two conditions together it doesn't work.

this doesn't work - "Type Mismatch"
DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] >= " & "'" & bdt & "'" And "[shipped_date] <= " & " '" & edt & "'"


Please take a look. Thanks!

 

[alicejwz]

The syntax you have is correct. I think it's going to work.

Thank you very much!
Have a good day!