Format Code for dates
---
Hi Franklin,
fieldnames containing spaces or special characters need to be enclosed
in brackets. Personally, I just use letters, numbers, and underscore in
names -- and ALWAYS begin names with a letter
Date needs to come out of the string so it is evaluated and its value is
concatenated in -- also, it needs a delimiter on both sides
mNextNum = Nz(DMax("[Julian Date]"
, "Awd_Ctrl_Log"
, "Awd_Ctrl_No = #" & Date & "#")
, 0) + 1
Is Awd_Ctrl_No a date data type? If so, it is not named well. If not,
it cannot be compared to a date
you can use the Format function to extract information about a date.
For instance, in the Format Code:
y --> Display the day of the year as a number (1 – 366)
yy --> Display the year as a 2-digit number (00 – 99)
format(#3/13/07#,"yy-y") --> 07-72
these examples are for Date is 3-13-07
format(Date,"yy-y") --> 07-72
format(Date,"yy") & "-" & format(format(Date,"y"),"000") --> 07-072
for more information about Format Codes for dates, look in help under
User-Defined Date/Time Formats (Format Function)
to get there quickly:
press CTRL-G for the Immediate (Debug) window
type
Format and press the F1 key while you are still on the word
then, click 'See Also'
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Crystal,
I have two different fields for holding data that I combine on a report to
give me the Julian Date and Award Number for the day. 07 is the year, 071 is
the day of the year (stored as a medium date but displayed as a Julian Date),
and the -01 is the first award order written for the day.
So the date field=Julian Date
The award order field=Awd_Ord_No, and this is the field I'd like to
automatically be populated with a "1" if I'm entering the first award order
for the day, and automatically increase by 1 for every other order I enter
for the day.
I inserted the following in the before event field:
Private Sub Form_BeforeInsert(Cancel As Integer)
mNextNum = Nz(DMax("Julian Date", "Awd_Ctrl_Log", "Awd_Ctrl_No= & Date &
"#"), 0)
End Sub
I get a compile error: Expected: List separator or ) at the last # in the
string
strive4peace said:
Hi Franklin,
consider something like this on the BeforeInsert event of your form:
mNextNum = nz(dmax("fieldname" _
,"tablename" _
,"datefield=#" & date() & "#"),0)
nopw that I am reading a bit more, I see you want something in this format:
07-071-1
what does 07-071 mean in terms of the date?
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
I'd like to have a number automatically populated based on a date.
I set a text box format to: =IIF(date())=([Date
Field]),([Awd_Order_no]+1),1) in hopes of accomplishing the following:
If todays date is the same date as the last (most recent) record, then add
one to the last records order number, and record this value in my table.
Otherwise, if this is the first entry for the day, make the value
automatically 1 (and I'm hoping this will be recorded in my table).
So that it would read 07-071-1 for the first order made for the day,
07-071-2 for the second, and automatically go to 07-072-1 for the first entry
tomorrow.
I posted this on a previous Field=Julian Date question but think that old
post is buried...not sure how these things get tracked here.
