Passing variable to SQL query, no records returned.

[d4zza]

I'm using Access 97 SR-2 on Windows XP to develop an Inventory style database that has some functionality, but having a problem making my query dynamic. I have a switchboard style form which I choose from a selection of 3 command buttons to select a Due Date for record reviews, choices being 30 days, 60 days, and 120 days. When I click on one of the buttons, A public variable, mqryDueDate, is set, for example, to today's date + 30 days, and is of 'Date' type in the declarations. This variable is then used in my query to pull all records which have a due date within the next 30 days. Simple stuff I thought, but the way I have my code and SQL statement, I have no records returned. If I have the query prompt me to manualy enter a date, the correct records ARE returned, but it's when trying to pass the variable data through to the query, nothing is returned. Using the command button's OnClick event, the following (relevant) VBA code is run...

 
mqryDueDate = Date + 30
 
DoCmd.OpenReport "rptDueDate", acViewPreview
 
 

The report's record source is a query called qryDueDate, and contains a SQL statement which is supposed to capture the value of variable mqryDueDate and return records based on..

 
WHERE (((AllInstructions.DueDate)< [Forms]![frmReportByDueDate]!mqryDueDate));
 
 

However, no results are returned, and no error is received.

I have had a look at various posts regarding this kind of situation, but no resolve was found. I am suspecting it's that my version of Access isn't able to do this, or I've typed in the WHERE statement wrong.

Any help would be appreciated, and if I've forgotten to provide any information (as per posting guidelines), I do apologise.

thanks...

[TheSmileyCoder]

Is mqryDueDate bound? I suspect maybe if its unbound, access doesn't know that your storing a date type in the field, and formats it as a string instead? (this is just a guess)

You could try:

WHERE (((AllInstructions.DueDate)< & "#" & [Forms]![frmReportByDueDate]!mqryDueDate & "#" & ));

[d4zza]

Thanks for quick reply, however, I'm getting a syntax error (missing operator) when I cut and paste the amended SQL statement. I'm sure this is just something silly that I'm missing

Also, the variable is not bound. It's purely a public variable that is used and set only when the OnClick event occurs, and then used for query.

The OnClick vba code is simple only the two lines previously quoted (but obviously with start and end sub and error handler).

If it helps, here is the full SQL statement

SELECT DISTINCTROW AllInstructions.DocumentNumber, AllInstructions.IssueNumber, AllInstructions.DisciplineID,  AllInstructions.DocumentStatusID, AllInstructions.DueDate
FROM AllInstructions
WHERE (((AllInstructions.DueDate)< & "#" & [Forms]![frmReportByDueDate]!mqryDueDate & "#" & )); 
 

and error...

Syntax error (missing operator) in query expressions '(((AllInstructions.DueDate)< & "#" & [Forms]![frmReportByDueDate]!mqryDueDate & "#" & ))'.

thanks...

[TheSmileyCoder]

Is mqryDueDate a variable or a textbox on the form? I dont think the SQL engine can see the variables, even if public (but im unsure, I have never tried)

[TheSmileyCoder]

Well tried setting a public variable for the form, and referencing that from my query, but no such luck. I didn't get any error msg, just no returned records.
I created a unbound textbox (visible=false) and set that using code, then opening query, referencing the Hidden textbox in my criteria, and that worked just fine.

[d4zza]

It's a variable. The form has 3 command buttons for selecting a Due Date (and 1 to exit). When the command button is clicked, the OnClick event runs the VBA code to set mqryDueDate to the selected date.

It would make sense to me (disappointingly) that it doesn't work if the SQL engine can't see the variable, and I suppose I'll have to come up with another method of creating a dynamic query for reports.

I may even just have users enter the date manually, not ideal for my purposes, but whatever works will have to do.

thanks...

[d4zza]

I'll try using the unbound text box just now and post my result.

thanks...

[d4zza]

I created a unbound textbox (visible=false) and set that using code, then opening query, referencing the Hidden textbox in my criteria, and that worked just fine.

I've tried doing this, but it's not working. I do know, however, that it's because I'm not sure how to do this.

If I call the textbox txtqryDueDate, can you provide the code to do this please, and what the SQL query would then be?

thanks...

[TheSmileyCoder]

Should be as simple as this:

  'Set Date
    Me.txtqryDueDate=Date+30
  'Open report
    DoCmd.OpenReport "rptDueDate", acViewPreview 

And I was wrong about the # , they are not needed if your using a textbox.

WHERE (((AllInstructions.DueDate)< [Forms]![frmReportByDueDate]![txtqryDueDate]));

[d4zza]

Thanks, Smiley. That works ideally!!! :)

When I tried it first time, I missed the "me." when setting the value! yeah, silly, I know. :doh:

thanks...