Date Format of the Serial Number of the Date

[Bob Barnes]

IE..Format(Now(), "mmm")..

What is used to find the "number" of the date in a span of many years?
...if I'm asking this correctly. I need to sort ascending from something
like 1/1/05 thru 3/23/08.

TIA - Bob

 

[Wayne-I-M]

You would need to leave the date column in the query and then format a
calculated column as the "format" option will give you a string which will
not sort the way a date will.

 

[Bob Barnes]

Wayne - I've stored the Date/Time Field as a "General Date".

I feel silly...how do I extract the "3/23/08" from the stored Field as a
"General Date"?

TIA - Bob

 

[Bob Barnes]

Looks like..Int([AWhen]), where my "General Date" Field is "AWhen" will solve
this.

TIA - Bob

 

[Wayne-I-M]

is the data stored as a "date/time" or as text

If it's a "date/time" you can display it as you want - set the format in the
table in design mode. Note tha this will not alter the actual data stored
just the way in which you can see it. Or is you ant to convert it to a
string you can use Format option or to get just the date section of the field
you can use DateValue



Sorry but I may be misunderstanding (is that a real english word - I hope
so?) what you are trying to do

 

[Bob Barnes]

Wayne - It's stored as "date/time"...I used Int(TheField) in a Query to set
what I needed.

DateValue will also work.

Thank you - Bob

 

[Wayne-I-M]

I'm glad you got it OK.

 

[Albert D. Kallal]

IE..Format(Now(), "mmm")..

I should point out that using "now()" will INCLUDE a time portion, and that
means you can't use date values for your query, you have to include time.

MM/DD/YYYY hh:mm

You won't be able to go:


select tblInvoice where InvocieDate = #11/01/2008#

The reason for this is your using now().

You should be using the date() function, not now() function is you don't
need the time part. I cannot tell you how many people in this newsgroup have
absolute trashed their databases because they used now() as a default, and
therefore cannot query any date saved since that date has an EXACT time
portion saved with the date field......

Of couse, if you need the timeportion, then use now()....

 

[Wayne-I-M]

Hi Albert

I just tried this (using the detail given by Bob Barnes) and it works fine ?
I set the defualt to Now() and this finds the data set by a date criteria


SELECT Table1.Date1
FROM Table1
WHERE (((Table1.Date1)=#3/23/2008#));

 

[Albert D. Kallal]

Hi Albert

I just tried this (using the detail given by Bob Barnes) and it works fine
?
I set the defualt to Now() and this finds the data set by a date criteria


SELECT Table1.Date1
FROM Table1
WHERE (((Table1.Date1)=#3/23/2008#));

If the default value for date1 is now(), then the above query will not work
correctly. Open up the table, and view the date1 column.....you should see a
time portion, and if you don't, then something else is at work here.

I suppose it needs pointing out that Bob's example was actually asking about
sorting, and his expression used now(), we actually don't know what he is
uses for the default field. And, if you do using month(), day(), year(), and
datevalue()...you will get whole numbers.

It just that in his post, using now was a VERY wrong choice. Test your above
conclusion, something else is at play here....if you actually included the
time portion in your date field, your above where expression will not
work...

 

[Bob Barnes]

There IS a problem w/ "DateValue" -- it gives something like 3/23/2008.

In my Query, entering 3/23/08 doesn't work..as if it's treating it like a
string???

 

[John W. Vinson]

There IS a problem w/ "DateValue" -- it gives something like 3/23/2008.

In my Query, entering 3/23/08 doesn't work..as if it's treating it like a
string???

Please post (or repost, if you have previously) the entire query in SQL view.

You may need to define a Parameter. If you're entering a literal date rather
than a parameter, try

#3/23/08#

so that the value is perceived as a date.

 

[Bob Barnes]

John - I used a DateValue of a Date/time Field stored as "General
Date"...based on a Card Swipe...
R = "SELECT * FROM Swipes;"
Set RT = Z.OpenRecordset(R, dbOpenDynaset)
With RT
.AddNew: !MemSwipe = Finder: !AWhen = Now()
!Spot = Text30: .Update: .Close: Set RT = Nothing
End With

I wanted to store the Date and Time to track Card Swipes at a Supper Club.
"Extracting" the date by using DateValue shows, IE, 3/23/2008...

I have unbound fields using a where "Between Forms!Members!ASwipe and
Forms!Members!ASwipe"...it worked after I formatted the ASwipe and BSwipe as
"mm/dd/yyyy"...formatting as "mm/dd/yy" did NOT work.

Does it shed the correct procedure to follow?

Thank you - Bob

 

[John W. Vinson]

John - I used a DateValue of a Date/time Field stored as "General
Date"...based on a Card Swipe...
R = "SELECT * FROM Swipes;"
Set RT = Z.OpenRecordset(R, dbOpenDynaset)
With RT
.AddNew: !MemSwipe = Finder: !AWhen = Now()

Now() does not add the date. It adds the date AND TIME, accurate to a few
microseconds.

!Spot = Text30: .Update: .Close: Set RT = Nothing
End With

I wanted to store the Date and Time to track Card Swipes at a Supper Club.
"Extracting" the date by using DateValue shows, IE, 3/23/2008...

I have unbound fields using a where "Between Forms!Members!ASwipe and
Forms!Members!ASwipe"...it worked after I formatted the ASwipe and BSwipe as
"mm/dd/yyyy"...formatting as "mm/dd/yy" did NOT work.

Usually Between() is used to select a RANGE of values and you're using the
same value for both. This will get the range of dates from (e.g.) 3/24/2008
20:04:34 to 3/24/2008 20:04:34. That's rather unlikely to find anything.

Does it shed the correct procedure to follow?

Thank you - Bob

If you're storing Now() as a date/time, that's fine; but how do you want to
retrieve it? It sounds like you are swiping the card a couple more times (what
are ASwipe and BSwipe and why aren't they in the query?)

Please post the ENTIRE query that you're using; what would be typical values
of ASwipe and BSwipe; and what records you expect to retrieve.

 

[Bob Barnes]

John...

Now() does not add the date. It adds the date AND TIME, accurate to a few
microseconds.

I did that to use one Field instead of two (which would have stored Date and
Time separately).

Usually Between() is used to select a RANGE of values and you're using the
same value for both. This will get the range of dates from (e.g.) 3/24/2008
20:04:34 to 3/24/2008 20:04:34. That's rather unlikely to find anything.

I'm using the Calendar Control to populate the dates, which are usually a
span, rather than a single day.

If you're storing Now() as a date/time, that's fine; but how do you want to
retrieve it? It sounds like you are swiping the card a couple more times (what
are ASwipe and BSwipe and why aren't they in the query?)

ASwipe and BSwipe are the From..To unbound controls populated by code w/ the
Calendar Control.

Please post the ENTIRE query that you're using; what would be typical values
of ASwipe and BSwipe; and what records you expect to retrieve.

I am not at that Client. I use DateValue of the Field "AWhen" (the Now()),
which displays as a 4-digit year - IE..3/24/2008. The criteria for that
calculated Field is the "Between..and.." previously mentioned. As long as
ASwipe and BSwipe are 4-digit years, it works.

Other thoughts? Thank you - Bob

 

[John W. Vinson]

ASwipe and BSwipe are the From..To unbound controls populated by code w/ the
Calendar Control.


I am not at that Client. I use DateValue of the Field "AWhen" (the Now()),
which displays as a 4-digit year - IE..3/24/2008. The criteria for that
calculated Field is the "Between..and.." previously mentioned. As long as
ASwipe and BSwipe are 4-digit years, it works.

Makes no sense to me!!! The actual date value does not HAVE a two-digit year
or a four-digit year. There's something odd about either your query, or the
data in your table.

Have you specified a Date/Time Parameter for the query?

 

[Bob Barnes]

Have you specified a Date/Time Parameter for the query?

John - I hadn't..but will. Thank you - Bob