Filtering records using sql in combo box

[Mbowe]

Please help! I am trying to do a very simple query. I just want to extract data from the table (UK_Members) based on the date (PAID_THRU) selected in a combo box (cboPaid_Thru). It does what I want but after selecting date in a combo box, it always asks me to enter parameter values again for cboPaidThru.

Code:

Dim sql As String

sql = "select * from UK_Members where PAID_THRU = cboPaidThru.Value;"
DoCmd.OpenForm "UK_Members_Form", acNormal, sql

What am I missing?

 

[PHV]

sql = "select * from UK_Members where PAID_THRU = #" & Format(Me!cboPaidThru.Value, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mbowe]

Thanks PHV, it helped me to avoid the parameter values needed, now I am able to go straight to the form but no records retrieved from the table, just blank form. Does this have to do with the data format or I miss something else? Thanks

 

[PHV]

What is the data type of UK_Members.PAID_THRU ? Hopefully DateTime.
If by chance this field holds time value:
sql = "select * from UK_Members where Int(PAID_THRU)=#" & Format(Me!cboPaidThru.Value, "yyyy-mm-dd") & "#"

You may also play with the IsDate function to check cboPaidThru

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mbowe]

PHV, I will change the data types to DateTime. Also I might have to re-arrange my table again because I found out that the paid_thru field is repeated (duplicated). It's (paid_thru) also in the same table as the other data to be retrieved. Thanks a lot. I am not sure if this creates a problem.