Date Filtering

[Amerlitong]

I have this table and a Date column(YYYY-MM-DD format) and i assumed this is a TEXT.


I made this query linked to a textbox(i set the input mask same format as it is in my table).
This is the code.

SELECT * FROM tblData WHERE Col_Date LIKE "*" & FORMS!frmMain![txtDate] & "*"


Please advise.

P.S.

the table is imported from excel file.


Thanks

[Missinglinq]

...I made this query linked to a textbox...

Putting aside, for the moment, that fact that you're trying to match a Text value with a Date value, how did you manage to "link a textbox" to a Query? You cannot use a Query or SQL statement as the Control Source for a Textbox. For this kind of thing you'd have to use the DLookup function.

Linq ;0)>

[alcapps]

Putting aside, for the moment, that fact that you're trying to match a Text value with a Date value, how did you manage to "link a textbox" to a Query? You cannot use a Query or SQL statement as the Control Source for a Textbox. For this kind of thing you'd have to use the DLookup function.

Linq ;0)>

If you update a query with a string then execute the query you can use a textbox to make run a query.


qdf.sql = "select ........"
qdf.close

docmd.openquery("query you changed")


however you right to about the date and string where clause.
I would change the string.

SELECT * FROM tblData WHERE Col_Date LIKE "*" & FORMS!frmMain![txtDate] & "*"

to
to set the qdf.sql
"SELECT * FROM tblData WHERE Col_Date = #" & FORMS!frmMain![txtDate] & "#"

hope this helps

[Amerlitong]

Putting aside, for the moment, that fact that you're trying to match a Text value with a Date value, how did you manage to "link a textbox" to a Query? You cannot use a Query or SQL statement as the Control Source for a Textbox. For this kind of thing you'd have to use the DLookup function.

Linq ;0)>

Sorry if i mentioned it wrong...i just want to say, i have this query that get the parameter from a textbox in a form...

Cheers

[Amerlitong]

If you update a query with a string then execute the query you can use a textbox to make run a query.


qdf.sql = "select ........"
qdf.close

docmd.openquery("query you changed")


however you right to about the date and string where clause.
I would change the string.

SELECT * FROM tblData WHERE Col_Date LIKE "*" & FORMS!frmMain![txtDate] & "*"

to
to set the qdf.sql
"SELECT * FROM tblData WHERE Col_Date = #" & FORMS!frmMain![txtDate] & "#"

hope this helps

Thanks mate...i never thought about this # sign...but i have another problem...if i leave the textbox blank it should be filter all the data...

Please advise...

Thanks...

[alcapps]

Do an if statement like this looks at txtDate and if it is null makes it "" if a space was there.. Trim takes away all spaces then checks the length of the variable if it's greater than 0 does what you did before otherwise drop the where clause for all.

if Len(Trim(Nz(FORMS!frmMain![txtDate],""))) > 0 then
select * from with #date#
else
select * from tblData
end if

[Amerlitong]

Thats it...thank you so much mate...i need to do some VBA stuff...