yyyymm

[scottcraft]

I am wanting to use a table with the date entry of yyyymm and be able to find out when the date is 13 months old. example 200001 and 200101 it is fine, but at 200102 it becomes late. there are processes which must be updated yearly, I need to find out without changing the formula, how to do this. thanks

 

[dhookom]

What values are you actually storing in your table? Is the field text or date or numeric? Are you just asking how to create a query where a field value is 13 months old? Could you please share some field and table names?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[KenReay]

As Duane says more information would be useful

You could also try looking up Format() in help and and DateDiff()

useful starters might be Format(Date(),"yyyymm")

DateDiff("m",YourDate,Date())

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scottcraft]

I am new at this so I appologize for not giving enough information. I am in the US Army and developing a reporting system that shows when soldiers are due periodic physicals (required every 5 years) and annual Physical Fitness Tests. The date for each LAST_PHYS and APRT are in a text format with each YYYYMM. The information is downloaded from a larger data base and reduced only to the 1,800 or so soldiers I have. I want to be able to create a query that show soldiers who HAVE both and are current and also create a report which shows when one or both of the entries are expired. I hope this provides enough info for someone to assist.

thanks

 

[KenReay]

Hi

Ok so you need something like

"SELECT * FROM YourTable WHERE Current=True AND (DateDiff("m",CDate([Last_Phys] & "01"), Date()) >= 13 OR DateDiff("yyyy",CDate([aprt]&"01"),Date()) >=5);"

I have guessed at teh name of your table and the column which tells you the soldier is current

I may have Last_Phys and aprt the wrong way around, but I am sure you will get the idea

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scottcraft]

Ken

where do I do the "SELECT ......" from. Am I creating a Query? Can I leave the dates as text or must they be converted to be read. It is a download form the Army will they have given me special permission to receive my data directly from them. If you don't mind, can you spell it out for a beginner.

thanks in advance