Cannot import & use military date format (yymmdd)

[Fastboatz]

I am brand new to Access.

I believe I am using Access 2000 at work and also using 2003 at home.

I am inporting data from PCEDVR program into excel. Cleaning it up and the transfering into access.

The date data comes in two formats:

short= yymm
med= yymmdd

I have tried changing regional settings. I have looked through the help several times. And have been searching different threads on here for about 2 hours.

What I need to be able to do is import the date data in the military format, keep it in that format , with no seperator, and be able to do queries using the same format ie;

yymmdd= 061111

querie= all between today and + 15 months.

I know I am not speaking program language. I would not want to insult anyone with the feeble attempt. I would really appreciate any help with this. Thank you.

MA1(SW), USN

[NeoPa]

Firstly, it is important to understand that SQL (the query language used for pretty well all queries no matter which provider is used) always uses, exclusively, and without exception, the M/D/Y format when dealing with dates. To repeat myself for clarity, Regional Settings do NOT come into the equation - ever (Why they chose this format above the military format - designed with practicality in mind as well as region independent anyway, is beyond me).
Correction, FIRSTLY it is good to know that you've attempted to find an answer using normal channels before dropping in here with a question.
Moving on...
Clearly, for your purposes, we need to convert one or the other before we can make comparisons.
To convert a date to your format is very straightforward, as there is a built-in and very flexible function (Format()) which does that job for you. In your case

Format([YourDate],'YYMMDD')) or Format([YourDate],'YYYYMMDD'))

I will assume from your question that the military date format uses 2-digit years exclusively (I sometimes use a similar format (but with 4 years) for reasons of practicality).
I have a function (stored in an ordinary 'Module' - not a 'Class Module'.) which can convert your date for you.
This is doctored for your format.

'YMDToDate takes varDate in YYMM or YYMMDD format and returns it as a date.
'If the result is not a valid date it is returned, as modified, but as a string.
Public Function YMDToDate(varDate As Variant) As Variant
    Dim strDMY As String
 
    strDMY = Mid(varDate, 3, 2) & "/" & Left(varDate, 2)
    If Len(varDate) = 6 Then strDMY = Right(varDate, 2) & "/" & strDMY
    If IsDate(strDMY) Then
        YMDToDate = CDate(strDMY)
    Else
        YMDToDate = strDMY
    End If
End Function

It is important to bear in mind when working with converted data, exactly what form the results are in.
Stating the obvious (the bits we often overlook when focusing on a problem) if it's converted to a string it will only respond as one and cannot have date manipulation performed on it (and vice-versa of course).
Good luck with the rest of your project.

[NeoPa]

CORRECTION:
My code works in England - Doh!
I'm guessing from your signature that that's not your normal field of operations.
Here's a version using native USA format (this is important for the CDate() function) 'M/D/Y'.

'YMDToDate takes varDate in YYMM or YYMMDD format and returns it as a date.
'If the result is not a valid date it is returned, as modified, but as a string.
Public Function YMDToDate(varDate As Variant) As Variant
    Dim strDMY As String
 
    strDMY = Mid(varDate, 3, 2) & "/" & Left(varDate, 2)
    If Len(varDate) = 6 Then strDMY = strDMY & "/" & Right(varDate, 2)
    strDMY = strDMY & "/" & Left(varDate, 2)
    If IsDate(strDMY) Then
        YMDToDate = CDate(strDMY)
    Else
        YMDToDate = strDMY
    End If
End Function

I did try to edit it but 5 mins after the post it's not an option any more.

[PEB]

Hi,

Before inserting your data in Access you need to change the format in your dates.

If you can't do it..

Create a table that the field where will be stored the date should be text..

The text in this format will be accepted...

When it's done you need to do from queries -> New query

Add the table with imported information

From query choose Update query

Choose your Column with the date

in the field update to

write down:

Format(CVdate([MYDATE]),"DD/MM/YYYY")

[MYDATE] is your field Name with the respective date
And this is the format that you want..

But before running this query try to change your regional settings of your machine like the format that you have specified...

IF this isn't possible use the codes from NeoPa

[Fastboatz]

Thank you so much for taking the time to assist me on this. I ask of you a little more patience as I admit.... I do not know where to apply the code that is written. I have been looking through the design templates and mirroring fields into my database such as SSN Field format = 000\-00\-0000, but I cannot find anything as advanced as the code you applied for me. Does it go in several areas or is there one spot to apply it.
I am VERY VERY new to all of this. I tried a free online tutorial and learned a little about queries and relationships and such, but it feels like I need an IT degree to create this seemingly basic database. :-(

this is an example of my database...

[name][ssn(primary key/required/indexing/no dups)]active duty service date(yymmdd)][planned rotation date(yymm)][end of active duty obligated service(yymmdd)]

Not written in program language used symbols to indicate [field] and (amplifying information)

I hope this makes sense and I don't sound like too much an idiot, but no this is not my normal line of work. Usually I am either fully armored up with a few weapons providing security in diffeerent parts of the world, or driving a really fast boat with lots of bigger weapons in different parts of the world. As funny as it sounds, that seems so much easier than trying to do this database!!!!!


Thank you all for your time and effort to assist me.

MA1(SW)
[IMG]

CORRECTION:
My code works in England - Doh!
I'm guessing from your signature that that's not your normal field of operations.
Here's a version using native USA format (this is important for the CDate() function) 'M/D/Y'.

'YMDToDate takes varDate in YYMM or YYMMDD format and returns it as a date.
'If the result is not a valid date it is returned, as modified, but as a string.
Public Function YMDToDate(varDate As Variant) As Variant
    Dim strDMY As String
 
    strDMY = Mid(varDate, 3, 2) & "/" & Left(varDate, 2)
    If Len(varDate) = 6 Then strDMY = strDMY & "/" & Right(varDate, 2)
    strDMY = strDMY & "/" & Left(varDate, 2)
    If IsDate(strDMY) Then
        YMDToDate = CDate(strDMY)
    Else
        YMDToDate = strDMY
    End If
End Function

I did try to edit it but 5 mins after the post it's not an option any more.

[PEB]

Hi
If you choose to use code so you have to go in modules - > New and Paste the function given by NeoPa there!

Than go in queries use Update query.. maybe here is better to say you how it works...

But for that get the links on the top of the Access forum!

And when the Update query is clear

Paste into "Update to" part under the column with your date Fill the brackets with [The name of your date field]

And run it!

So far so good!

[NeoPa]

Before inserting your data in Access you need to change the format in your dates.
...
Format(CVdate([MYDATE]),"DD/MM/YYYY")

Be careful PEB.
This is not the way to go.
The format of the dates is necessary for both situations.
Fastboatz must display his dates in military format (yymmdd).
SQL must have its dates passed in SQL format (m/d/y) which is standard across all SQL.
d/m/y - as you've used is restricted to European countries. In USA - outside of the military - they generally use m/d/y. I would guess that it's no co-incidence that this matches exactly the SQL format (the other way around in reality).
Personally, I think they should have used the military format, which is logical and in the best order for sorting.

Correction: Keep confusing military format (yymmdd) with yy/mm/dd (which I feel is best format for SQL standard. Doesn't really matter as they forgot to ask me :(.

BTW Thanks for explaining where (and how) to put the code in - saved me a job.

[PEB]

Yeap Format() should't work in this case...but not sure....


he he he

See what i've put in my Regional Settings

yyMMdd

And when I paste a dates like this Access accepts them!

So my friend change your Regional Settinfgs to yyMMdd as Date and import your date in Access with simple Copy Paste Don't dream codes, coding, manuals, programmers ... Forget ... No more! Just touch your regional Settings!

[IT2E]

I am brand new to Access.

I believe I am using Access 2000 at work and also using 2003 at home.

I am inporting data from PCEDVR program into excel. Cleaning it up and the transfering into access.

The date data comes in two formats:

short= yymm
med= yymmdd

I have tried changing regional settings. I have looked through the help several times. And have been searching different threads on here for about 2 hours.

What I need to be able to do is import the date data in the military format, keep it in that format , with no seperator, and be able to do queries using the same format ie;

yymmdd= 061111

querie= all between today and + 15 months.

I know I am not speaking program language. I would not want to insult anyone with the feeble attempt. I would really appreciate any help with this. Thank you.

MA1(SW), USN

MA1,

IT2 Here.. There is a way that you can enter that data into access, although you will have to go into the design view of the table. You will not be able to use tge date option in data type. If you use number text for the field instead you should be able to make that work.

Now here is a question for you.. Do you or anyone at your command have the instructions on how to install PCEDVR and it's databases??

IT2E