Link to home
Start Free TrialLog in
Avatar of snyperj
snyperjFlag 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

Avatar of Kent Dyer
Kent Dyer
Flag of United States of America image

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of snyperj

ASKER

This worked.  Thanks.