Avatar of snyperj
snyperj
Flag for United States of America asked on

Modifying query parameters in code

When I run the below code, it modifys the query but it puts the form criteria as the parameter and not the literal values.  How do I re-write this so the criteria for TransDate becomes Between 7/11/2011 and 7/12/2012 instead of:   Between [Forms]![F_PRINT_ACTIVITY]![t_start] And [Forms]![F_PRINT_ACTIVITY]![t_end])

Thanks

 
Set qdf = CurrentDb.QueryDefs("Q_RECEIVING_ACTIVITY_REPORT_XLT")
                ssql = "SELECT * From Q_RECEIVING_ACTIVITY_REPORT " & vbCrLf
                ssql = ssql & "WHERE(TransDate Between [Forms]![F_PRINT_ACTIVITY]![t_start] And [Forms]![F_PRINT_ACTIVITY]![t_end])"
                qdf.sql = ssql

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
snyperj

8/22/2022 - Mon
Kent Dyer

How about DateAdd?

http://www.w3schools.com/Vbscript/func_dateadd.asp

If memory serves, Access supports DateAdd..

That should do it..

HTH,

Kent
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Try this:

Set qdf = CurrentDb.QueryDefs("Q_RECEIVING_ACTIVITY_REPORT_XLT")
                ssql = "PARAMETERS [Forms]![F_PRINT_ACTIVITY]![t_start] DateTime, [Forms]![F_PRINT_ACTIVITY]![t_end] DateTime;"
                ssql = ssql & "SELECT * From Q_RECEIVING_ACTIVITY_REPORT " & vbCrLf
                ssql = ssql & "WHERE(TransDate Between [Forms]![F_PRINT_ACTIVITY]![t_start] And [Forms]![F_PRINT_ACTIVITY]![t_end])"
                qdf.sql = ssql
snyperj

ASKER
This worked.  Thanks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy