Daily Number and Date

[Joe Y]

Hi,

I am trying to build a date coding system in Access. A set of number is automatically generated on daily basis started from 1 everyday. These data are stored in a table call T_Job with following fields.

Job_ID
Production_Date
Recipe_ID
Recipe_Ver
QTY
UOM
DailyNumber

In the active form, I have a text box named txtProductionDate that user can enter production date. The date value from this text box is then passed to a vba variable called ProdDate.

In the vba, I tried this code just to see if this approach works.

 
Dim ProdDate As Date
 
ProdDate = Me.[txtProductionDate].Value
 
MsgBox Nz(DMax("[DailyNumber]", "[T_Job]", Production_Date = ProdDate), 0), vbOKOnly
 

However, the message box always return 0 perhaps the DailyNumber is constantly being null.

On the other hand, when I tried the code in the Immediate panel below the vba editor, the message box returns correct number.

Can anyone tell me what went wrong with the code?

Thanks,
Joe

[PhilOfWalton]

Try

 
MsgBox Nz(DMax("[DailyNumber]", "[T_Job]", 
"Production_Date = #" & ProdDate) & "#", 0), vbOKOnly
 

There are 2 problems with your code
1) The Where condition needs quotes("") round it.
2) Date comparisons need a Hash (#) on either side of the date.

Although not relevant to your current question, Text comparisons need quotes round them, but this tends to behard to read so I use Chr$(34) instead.

[jforbes]

I would recommend putting Option Explicit at the top of your code modules. Having Option Explicit turned on would have pointed out that "Production_Date" was an undefined variable.

[Joe Y]

Phil,

Adding # on either side of date comparison works. I had to make small format changes from your code, but it works!

Many thanks.
Joe

[NeoPa]

Phil:
Date comparisons need a Hash (#) on either side of the date.

Hi Phil.

I would advise caution when giving advice about date formatting in SQL. It really isn't as simple as adding hashes (#) around a date string formatted in the default way by Access (See Literal DateTimes and Their Delimiters (#)). This is a particular problem as it will seem to work in most cases because Jet/ACE does some automatic recognition of the string so only those which can be ambiguous are ever seen to fail. It is quite important to mention that the date needs to be formatted in one of the specified ways in order for it to be reliable.

JForbes:
Having Option Explicit turned on would have pointed out that "Production_Date" was an undefined variable.

I would never argue against that J, but in this case I believe "Production_Date" was intended to be a reference to a field rather than a VBA variable. The OP forgot to add the quotes which would have made it all clearer.

[zmbd]

This question appears to be a variation on the theme:
home > topics > microsoft access / vba > insights > item numbering within a group .

Of course, this article references a yearly cycle; however, with a few modifications it should be useable for daily cycles. I haven't reviewed the article's code for such usage; however, if OP desires we can work thru that here.

-Z