Formatting Date

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

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


"JIM" wrote in message
...
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

On Wed, 7 Jan 2009 20:41:28 -0800, JIM wrote:

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 [MVP]

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

"John W. Vinson" wrote:

On Wed, 7 Jan 2009 20:41:28 -0800, JIM wrote:

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 [MVP]

Thanks Tina, it works perfectly.

"tina" wrote:

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


"JIM" wrote in message
...
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

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

"John W. Vinson" wrote:

On Wed, 7 Jan 2009 20:41:28 -0800, JIM wrote:

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 [MVP]

On Thu, 8 Jan 2009 13:05:03 -0800, JIM wrote:

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.
--

John W. Vinson [MVP]

you're welcome


"JIM" wrote in message
...
Thanks Tina, it works perfectly.

"tina" wrote:

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


"JIM" wrote in message
...
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