Manipulating Dates

[Guest]

I have a form with a button that will preview a report based on the selection
of the form. On the report, there are 2 relevant fields: ProductionDate &
FactoryDeliveryDate. Both of them are date fields. Also on the form, there is
a text field for user to enter a date, called txtDate.

OK, here's what I need to do. I need to generate a report where the
ProductionDate is null where FactoryDeliveryDate + 3 weeks => txtDate.

The button that opens the report in the On Click event looks something like
this:

Dim stDocName1 As String
Dim strWhere As String

strWhere = "[lngCustNo]=" & Me.cboCust And DateAdd("ww", 3,
FactoryDeliveryDate) => Me.txtDate
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , strWhere

I knew it was a long shot and as expected, it didn't work (type mismatch
error or something like this). Anyone can help if this is the right way to do
this? Thanks.

ck

 

[Rick Brandt]

I have a form with a button that will preview a report based on the
selection of the form. On the report, there are 2 relevant fields:
ProductionDate & FactoryDeliveryDate. Both of them are date fields.
Also on the form, there is a text field for user to enter a date,
called txtDate.

OK, here's what I need to do. I need to generate a report where the
ProductionDate is null where FactoryDeliveryDate + 3 weeks => txtDate.

The button that opens the report in the On Click event looks
something like this:

Dim stDocName1 As String
Dim strWhere As String

strWhere = "[lngCustNo]=" & Me.cboCust And DateAdd("ww", 3,
FactoryDeliveryDate) => Me.txtDate
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , strWhere

I knew it was a long shot and as expected, it didn't work (type
mismatch error or something like this). Anyone can help if this is
the right way to do this? Thanks.

strWhere = "[lngCustNo]=" & Me.cboCust & " And FactoryDeliveryDate >= #" &
DateAdd("ww", -3, Me.txtDate) & "#"

Whenever building a complex string variable it's a good idea to add...

Debug.print strWhere

....as a line of code immediately after assigning the string to the variable.
Then you can look at the resulting string in the debug window and see if it came
out the way you expected.

Also whenever possible use the expression on the criteria value you are
comparing to and not on the field (see how I swapped them around). It's more
efficient because if you use an expression on the field you immediately lose the
query engine's ability to utilize any index you have on the field.

 

[Guest]

Thanks Rick. It worked and thanks also for the tip.
ck

I have a form with a button that will preview a report based on the
selection of the form. On the report, there are 2 relevant fields:
ProductionDate & FactoryDeliveryDate. Both of them are date fields.
Also on the form, there is a text field for user to enter a date,
called txtDate.

OK, here's what I need to do. I need to generate a report where the
ProductionDate is null where FactoryDeliveryDate + 3 weeks => txtDate.

The button that opens the report in the On Click event looks
something like this:

Dim stDocName1 As String
Dim strWhere As String

strWhere = "[lngCustNo]=" & Me.cboCust And DateAdd("ww", 3,
FactoryDeliveryDate) => Me.txtDate
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , strWhere

I knew it was a long shot and as expected, it didn't work (type
mismatch error or something like this). Anyone can help if this is
the right way to do this? Thanks.

strWhere = "[lngCustNo]=" & Me.cboCust & " And FactoryDeliveryDate >= #" &
DateAdd("ww", -3, Me.txtDate) & "#"

Whenever building a complex string variable it's a good idea to add...

Debug.print strWhere

....as a line of code immediately after assigning the string to the variable.
Then you can look at the resulting string in the debug window and see if it came
out the way you expected.

Also whenever possible use the expression on the criteria value you are
comparing to and not on the field (see how I swapped them around). It's more
efficient because if you use an expression on the field you immediately lose the
query engine's ability to utilize any index you have on the field.