If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Formatting Date
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 |
#3
|
|||
|
|||
Formatting Date
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] |
#4
|
|||
|
|||
Formatting Date
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] |
#5
|
|||
|
|||
Formatting Date
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 |
#6
|
|||
|
|||
Formatting Date
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] |
#7
|
|||
|
|||
Formatting Date
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] |
#8
|
|||
|
|||
Formatting Date
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 |
Thread Tools | |
Display Modes | |
|
|