How to change date field to number field

  • Thread starter Thread starter postman
  • Start date Start date
P

postman

How to; in an append query, convert a Date Data type with format ddmmyy
(090505) to a Number Data type field in another table without typical date
syntax eg 09/05/2005. Just a number 090505.
Its for an Inventory number which i wish to append (join) to an Autonumber
to give:
01-090505
02-090505 etc.

Thanks
 
I assume you are not actually storing the combination of text-date and
autonumber ("01", "02", ...) in a field, but are using a query to
concatenate these together (hint, hint!).

If you use the Year(), Month(), and Day() functions to get the pieces of a
date, you can actually alter the value. If you use the Format() function,
you can alter the appearance.

If you are not adding/subtracting them, they are not numbers! I would
suggest that you are working with text characters that happen to be digits.
 
I assume you are not actually storing the combination of text-date and
autonumber ("01", "02", ...) in a field, but are using a query to
concatenate these together (hint, hint!).
Click to expand...
No I have a clean autonumber field 1,2,3, etc, formatted to look like
01,02,03...& a date/time field formatted ddmmyy, in the one inventory table,
I want to use the joined fields as the ID for an inventory number, because
the date (090505) info is useful at a later stage for running stock control
queries on. And being an ID key field (indexed, no duplicates) directly
linked to the stock item.
OR
do I make a separate table with an Autonumber field & a date/time field &
maybe a third field for an appended number that can be sent from the form,
join the two fields together with a query and then append them into an
autonumber field of the inventory table.
OR
do I build an append query, & with the code builder make two expression
fields one with data type date/time formatted ddmmyy & the other with the
autonumber from the table that has the autonumber field of the stock
inventory table. And then another expression field to concatenate those two
fields. The append query then runs when I enter stock into the inventory
table from a form control. Or do I use a union query (just found out about
them things!?)
Err..how am I going so far?
just want the autonumber field of the inventory table to be number-date
(autonumber-ddmmyy).
 
Perhaps a small confusion on my part about terminology. I tend to use the
Access definition of "autonumber" (i.e., the Autonumber data type). Your
last statement sounds like you might be using this word to mean an ID value
that the program creates, vs. something entered by a person. I'll still
point out that if you have the two components, you can create a query that
concatenates them for appearances sake.

Not sure I've added anything here...

Jeff Boyce
<Access MVP>
 
Back
Top