Query Criteria using a Dlookup string from another txtbox

[steve1111]

Hello all,

I have seen similar post but none seem to answer my issue. I have a table tblDateGroups, with two field, [Group] (like This Week, Next Week, Last Month, etc) and

 that holds the code for criteria for each group in a query. I know the code is fine (like next week = Between Date()-Weekday(Date())+8 and Date()-Weekday(Date())+14    ) because I tested before putting in the table. 

On my form I have a combo box that list all the [Group] and i hidden textbox that looks up the code based on the cbx after update...all that is good. 

However, when i try to requery the report the criteria is not working. I am simply using the date criteria to read the hidden text box with the code string. Forms!frmMainHome!subaWelcome.Form!txtCodeForQuery. 

the error code is "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

I would appreciate any help on what i am missing here.

 

[CJ_London]

you can't apply data like this in a query, only if you build the sql in vba - e.g.

sqlstr="SELECT * FROM MyTable WHERE myDate" & me.group.column(1)


or if your report/form has a recordsource something like your table name or 'Select * FROM myTable' then

docmd.openreport "myTableReport",,,"myDate" & me.group.column(1)

where me.group.column(1)=' Between Date()-Weekday(Date())+8 and Date()-Weekday(Date())+14"

Incidentally, GROUP is a reserved word, using it can have unexpected consequences, particularly in SQL

 

[steve1111]

Thanks CJ for the right direction, and the FYI on GROUP.