format question (access 2003)

Topic

New Reply

I have an date id values of 20081016A or 20081016B (10/16/2008) in a query. I would like combine the totals for these 2 date id values by doing a group by query. I need the date value ids to be equal (20081016). I know I can strip of the A or the B using a right function but the problem with this is sometimes the length of these date value ids can vary. I have same date ids stored like 2008116A or 2008116B (which is for 1/1/2008). Is there a way for me to add something to my query which will remove the last character regardless of the string length? Thanks…

Reply | Quote

Replies

To get the date only part:

Mid(DateField, 1, Len(DateField)-1)

Reply | Quote

Thanks Pat..

Reply | Quote

or :

Left(DateField, Len(DateField)-1)

Reply | Quote

>>…I have same date ids stored like 2008116A or 2008116B (which is for 1/1/2008). ”

I’m still trying to figure out how you get 1/1/2008 out of 2008116B!

I assume you meant to say 1/16/2008; but really, it could also be 11/6/2008!

You will need to pass the ID to a function that examines the ID and then makes some decisions as to how to handle it based on how long it is and the contents. But even then, I don’t know how you can decisively determine what date is represented by 2008116B.

Reply | Quote