Formatting Date

[JIM]

I have this code on the click event of a command button:

Private Sub Option158_Click()
'DoCmd.OpenReport "rptWorkOrders", , , "[Date()] = " &
Format(Me![txtWODate], "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

End Sub
I'm trying to print out a batch of work orders if they have today's date in
them but I can get the syntax right and I tried several different formats
found in discussion group. It either prints all the work orders or nothing.
What am I doing wrong?
TIA

 

[tina]

so in the table, that lies under rptWorkOrders, there is a field storing the
work order date, correct? is the field's DataType a Date/Time data type? and
what is the name of the field?

here's an example of a report criteria expression, which assumes that 1) the
work order date field in the table IS a Date/Time data type, and 2) the name
of the field is WODate:

DoCmd.OpenReport "rptWorkOrders", , , "WODate = #" & Date & "#"

the criteria expression is essentially a WHERE clause (think SQL, as in
queries) that runs against the *table(s)* underlying the report. (and the
same concept applies when you use criteria in an OpenForm action, btw.) so
the field on the "left side" of the equation has to be the name of a field
that exists in the report's RecordSource; note: sometimes, especially in
earlier versions of Access, you may have to bind the field to a control on
the report - even if you make the control invisible - so that Access will
"see" the field. and the value (in this case, the return value of the Date
function) on the "right side" of the equation must have the same DataType as
the field on the left side.

hth

 

[John W. Vinson]

I have this code on the click event of a command button:

Private Sub Option158_Click()
'DoCmd.OpenReport "rptWorkOrders", , , "[Date()] = " &
Format(Me![txtWODate], "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

End Sub
I'm trying to print out a batch of work orders if they have today's date in
them but I can get the syntax right and I tried several different formats
found in discussion group. It either prints all the work orders or nothing.
What am I doing wrong?
TIA

The second line

DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

would be correct - putting Date() inside brackets probably won't work at all,
and the Format is going all around the barn.

I think the real problem is that your WhereCondition is looking *in a form
textbox*. It should be looking in a field in rptWorkOrder's Recordsource. I'm
guessing that there is a table field named WODate; if so, your correct syntax
would be either

DoCmd.OpenReport "rptWorkOrders", , , "[WODate] = #" & Date & "#"

or, if WODate contains a date and time,

DoCmd.OpenReport "rptWorkOrders", , , "[WODate] >= #" & Date & _
"# AND [WODate] < #" & Date + 1 & "#"

 

[JIM]

Thanks John for input. Here's my code and now a one page Work Order is
printed full of #Error messages:
Private Sub Option158_Click()
DoCmd.OpenReport "rptWorkOrders", , , "[WODate] = #" & Date & "#"
End Sub

The date field is defined as a short date. What am I missing?
TIA

I have this code on the click event of a command button:

Private Sub Option158_Click()
'DoCmd.OpenReport "rptWorkOrders", , , "[Date()] = " &
Format(Me![txtWODate], "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

End Sub
I'm trying to print out a batch of work orders if they have today's date in
them but I can get the syntax right and I tried several different formats
found in discussion group. It either prints all the work orders or nothing.
What am I doing wrong?
TIA

The second line

DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

would be correct - putting Date() inside brackets probably won't work at all,
and the Format is going all around the barn.

I think the real problem is that your WhereCondition is looking *in a form
textbox*. It should be looking in a field in rptWorkOrder's Recordsource. I'm
guessing that there is a table field named WODate; if so, your correct syntax
would be either

DoCmd.OpenReport "rptWorkOrders", , , "[WODate] = #" & Date & "#"

or, if WODate contains a date and time,

DoCmd.OpenReport "rptWorkOrders", , , "[WODate] >= #" & Date & _
"# AND [WODate] < #" & Date + 1 & "#"

 

[JIM]

Thanks Tina, it works perfectly.

so in the table, that lies under rptWorkOrders, there is a field storing the
work order date, correct? is the field's DataType a Date/Time data type? and
what is the name of the field?

here's an example of a report criteria expression, which assumes that 1) the
work order date field in the table IS a Date/Time data type, and 2) the name
of the field is WODate:

DoCmd.OpenReport "rptWorkOrders", , , "WODate = #" & Date & "#"

the criteria expression is essentially a WHERE clause (think SQL, as in
queries) that runs against the *table(s)* underlying the report. (and the
same concept applies when you use criteria in an OpenForm action, btw.) so
the field on the "left side" of the equation has to be the name of a field
that exists in the report's RecordSource; note: sometimes, especially in
earlier versions of Access, you may have to bind the field to a control on
the report - even if you make the control invisible - so that Access will
"see" the field. and the value (in this case, the return value of the Date
function) on the "right side" of the equation must have the same DataType as
the field on the left side.

hth

I have this code on the click event of a command button:

Private Sub Option158_Click()
'DoCmd.OpenReport "rptWorkOrders", , , "[Date()] = " &
Format(Me![txtWODate], "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

End Sub
I'm trying to print out a batch of work orders if they have today's date in
them but I can get the syntax right and I tried several different formats
found in discussion group. It either prints all the work orders or nothing.
What am I doing wrong?
TIA

 

[JIM]

I have it working now. Tina had the right combination in her answer.
Thanks

I have this code on the click event of a command button:

Private Sub Option158_Click()
'DoCmd.OpenReport "rptWorkOrders", , , "[Date()] = " &
Format(Me![txtWODate], "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

End Sub
I'm trying to print out a batch of work orders if they have today's date in
them but I can get the syntax right and I tried several different formats
found in discussion group. It either prints all the work orders or nothing.
What am I doing wrong?
TIA

The second line

DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date & "#"

would be correct - putting Date() inside brackets probably won't work at all,
and the Format is going all around the barn.

I think the real problem is that your WhereCondition is looking *in a form
textbox*. It should be looking in a field in rptWorkOrder's Recordsource. I'm
guessing that there is a table field named WODate; if so, your correct syntax
would be either

DoCmd.OpenReport "rptWorkOrders", , , "[WODate] = #" & Date & "#"

or, if WODate contains a date and time,

DoCmd.OpenReport "rptWorkOrders", , , "[WODate] >= #" & Date & _
"# AND [WODate] < #" & Date + 1 & "#"

 

[John W. Vinson]

Thanks John for input. Here's my code and now a one page Work Order is
printed full of #Error messages:
Private Sub Option158_Click()
DoCmd.OpenReport "rptWorkOrders", , , "[WODate] = #" & Date & "#"
End Sub

The date field is defined as a short date. What am I missing?
TIA

Is there a field named WODate in rptWorkOrders' recordsource? Do you have
(unwisely) a field or control named Date?

Try

DoCmd.OpenReport "rptWorkOrders", , , "[WODate] = #" & Format(VBA.Date(),
"mm/dd/yyyy") & "#"

all on one line.

 

[tina]

you're welcome

Thanks Tina, it works perfectly.

so in the table, that lies under rptWorkOrders, there is a field storing the
work order date, correct? is the field's DataType a Date/Time data type? and
what is the name of the field?

here's an example of a report criteria expression, which assumes that 1) the
work order date field in the table IS a Date/Time data type, and 2) the name
of the field is WODate:

DoCmd.OpenReport "rptWorkOrders", , , "WODate = #" & Date & "#"

the criteria expression is essentially a WHERE clause (think SQL, as in
queries) that runs against the *table(s)* underlying the report. (and the
same concept applies when you use criteria in an OpenForm action, btw.) so
the field on the "left side" of the equation has to be the name of a field
that exists in the report's RecordSource; note: sometimes, especially in
earlier versions of Access, you may have to bind the field to a control on
the report - even if you make the control invisible - so that Access will
"see" the field. and the value (in this case, the return value of the Date
function) on the "right side" of the equation must have the same DataType as
the field on the left side.

hth

I have this code on the click event of a command button:

Private Sub Option158_Click()
'DoCmd.OpenReport "rptWorkOrders", , , "[Date()] = " &
Format(Me![txtWODate], "\#mm\/dd\/yyyy\#")
DoCmd.OpenReport "rptWorkOrders", , , "Me![txtWODate] = #" & Date

&
"#"

End Sub
I'm trying to print out a batch of work orders if they have today's

date
in

them but I can get the syntax right and I tried several different formats
found in discussion group. It either prints all the work orders or nothing.
What am I doing wrong?
TIA