Date Issues

  • Thread starter Thread starter jxbeeman
  • Start date Start date
J

jxbeeman

I'm relativly new at access and ran into some issues.

Question 1:
I'm in a make table Query and would like to create a new column with a
specific date (02/10/2008) in every row of that column (Note: I would like to
do this through the query and not through a table). (i've set it = to the
date but the formatting is the real issue, How do i get the format in
MM/DD/yyyy? All i seem to end up with is a bunch of numbers that aren't = to
the date. )

Question 2:
How would I subtract 2 dates in the make table query and end up with a
number of days?

Question 3:
Is there somewhere to set the data type when writing an expression in a
query without going into the table?
Ex. I would like to set a specific column to a date. --> Data
Type.Date/Time with a format of say MM/DD/YYYY.

Thanks for the help, Josh
 
jxbeeman said:
I'm relativly new at access and ran into some issues.

Question 1:
I'm in a make table Query and would like to create a new column with a
specific date (02/10/2008) in every row of that column (Note: I would
like to do this through the query and not through a table). (i've
set it = to the date but the formatting is the real issue, How do i
get the format in MM/DD/yyyy? All i seem to end up with is a bunch of
numbers that aren't = to the date. )
Click to expand...

Use the format property of the column or use the Format() function...

AliasName: Format(Date(), "mm/dd/yyyy")
Question 2:
How would I subtract 2 dates in the make table query and end up with a
number of days?
Click to expand...

DaysDifference: DateDiff("d", FirstDate, SecondDate)
Question 3:
Is there somewhere to set the data type when writing an expression in
a query without going into the table?
Ex. I would like to set a specific column to a date. --> Data
Type.Date/Time with a format of say MM/DD/YYYY.
Click to expand...

A DateTime DataType should be created if you use a field in the query that
produces a date (not necessarily a string of characters that happens to look
like a date to you). In that case you would NOT want to use the Format()
function because that returns a string, not a date.

Date formatting only affects how a date looks. It has no affect on how the date
is stored so worrying about that at the table level is unnecessary. You take
care of formatting in your forms and reports.
 
Can you post the SQL you have, so we can see what you are doing that is not
working?

Q1: My recommendation would be to type the field using the CDate ( )
conversion function. It might look something like:

SELECT zztemp.Field1, cdate("02/10/2008") as [SomeDate]
INTO zzTemp
FROM zzTemp

See Rick's comments for Q2 and Q3.

HTH
Dale
 
Back
Top