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

Last Comment

snyperj

8/22/2022 - Mon

Kent Dyer

7/12/2011

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)

7/12/2011

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)

7/12/2011

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

7/13/2011

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