MS Access MMYY format 1

[rugbyguy]

Help please,

I am trying to query a database (access 2003). The date that is stored in my database is in MMYY format (I know - not too swift of me for digging my own hole!)

I have created a query parametre that asks for 'beginning date' and 'end date'. The problem is that Access does not recognize this format as a date. There is also the question of system settings for date. How can I query 1102 ('start date') and 0204 ('end date')? How can I define the first 2 digits as month and the last 2 digits as year?

Any and all help appreciated.

Thanks

 

[SkipVought]

rugbyguy,

You stated, "The date that is stored in my database is in MMYY format"

A DATE is a NUMBER. The FORMAT is how you VIEW that NUMBER.

Today happens to be 38167. The FORMAT controls how you VIEW it.

So you have to use a format to match the INPUT. IF the date is input as MMYY, then it might be something like...
[tt]
Format([MyDateField], "mmyy") = Me!Start
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

rugbyguy,

You stated, "The date that is stored in my database is in MMYY format"

A DATE is a NUMBER. The FORMAT is how you VIEW that NUMBER.

Today happens to be 38167. The FORMAT controls how you VIEW it.

So you have to use a format to match the INPUT. IF the date is input as MMYY, then it might be something like...
[tt]
Format([MyDateField], "mmyy") >= Me!Start
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[scriverb]

Look up and use the DateSerial function.

Syntax

DateSerial(year, month, day)

The DateSerial function syntax has these named arguments:

Part	Description
year	Required; Integer. Number between 100 and 9999, inclusive, or a numeric expression.
month	Required; Integer. Any numeric expression.
day	Required; Integer. Any numeric expression.

Post back with questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[rugbyguy]

My only question is as follows; since I don't need to modify the table, should the

=Format([notification_date], "mmyy") >= Me!Start

be entered in the report in the control section? My 4 digit variable MMYY is called "notification_date". I tried entering the above and got an error.

Thank You

 

[Zion7]

Actually rugbyguy, it may be a little more problematic than it appears. Obviously, the data type for, the start & finish date fields, are not declared as date types.

I don't believe, however you format it, they wil come out according to your intent. 1102 should equal Nov 2002, but will not.
I believe you need minimum, 3 arguments, day, month, year, to qualify as a date. Any date formatting done on anything less, will produce unexpected results.
Maybe you can create a procedure to reformat your input, since the format is consistent...

Dim iMonth, iYear as integer
sDate as String, dDate as Date

iMonth = Left(txtStart,2)
iYear = Right(txtStart,2)

Since it seems, the actual day is irrelavent for your purposes, will 1 do, for every entry?

sDate = "#" & iMonth & "/1/" & iYear & "#"

dDate = CDate(sDate)

Loop through your records & append new results.

txtStart = dDate

...either way, unless there is a function to recognize or convert 1102, to a date (that I'm not aware of), your format is recognizable, that it wouldn't be difficult to modify very quickly, with a short procedure.

Hope this helps, good luck.

 

[scriverb]

As I pointed out earlier here is a existing function to convert your date representation:

DateSerial(CIng(Right(txtStart,2)),CIng(Left(txtStart,2)),1)

You can use this function whenever you reference your date and it will be analyzed by ACCESS as a date. No need to change your table or datatype.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[KenReay]

Hi

Can you please clarify the datatype of the MMYY column in the table?

Is it

a date/Time formatted as MM/YY

or

a four charcater text field

or

a numeric of some form (ie integer, long, etc etc)



Regards

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

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

UK

 

[scriverb]

Hello Ken, good question. I am assuming because of his initial posting indicating leading zeroes (i.e. 0204) that the answer to your question is text(4). It is this reason that it is not being recognized in any way as a date. As you know the ACCESS can recognize many text formats as a date to convert but not mmyy. It is for this reason that I suggested using DateSerial to convert it at least for his comparison if not to convert all of his mmyy values to a new field designated as Date/Time.

You are up late, what's the matter got a little insomnia?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[KenReay]

Hi Bob Scriver

I am not up late, I am up early, I am in UK it is 08:00 in the morning here

I suspect you are right and rugbyguy has made life doubly difficult for himself, it would have been betetr to hold the values as YYMM, then simply

WHERE YYMM Between Format(Forms!MyForm!Fromdate,"yymm") AND Format(Forms!myForm!ToDate) would have done the trick

now I would suggest

WHERE Mid(MMYY,3,2) & Left(MMYY,2) Between Format(Forms!MyForm!Fromdate,"yymm") AND Format(Forms!myForm!ToDate) should do the trick


Regards

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

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

UK

 

[scriverb]

Sorry Ken didn't even look at your signature. It is me with the insomnia here in Michigan, USA.

That is a great idea for a quick fix without modifying anything in his tables. Hopefully RugbyGuy will pickup on it and let us know if it works for him. He must be a UKer also if his handle is any indication. If he were here in the states he would have picked Football but of course you guys surely think football is a sport with a round ball. Right??

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[KenReay]

Hi

Yes, as you say it is a 'quick fix' it will not work if he has dates which precede year 2000, and it will cease to work when 3000 comes along, but that is hardly likely to bother him.

Yes, football is the national passion (although not mine), no sporting interests at all I am afraid.

Regards

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

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

UK

 

[Zion7]

Sorry ScriverB, I was going to make mention of your date serial suggestion. It was my first time, hearing of it, so when I looked it up, rather hastily last night, I didn't quite appreciate its "versitality". Its main purpose seemed to be to "Find a date', but invariably, it returns a Date type. Voila, one fell swoop.

my thoughts are ultimately, he's going to want to have his "date entries" as date types, for future purposes (considering it's such an easy maneouvre to do so.

but either way, your method is very good for an immediate comparison, and has enlightened me to a new function.
Thank-you for that.
..oh, by the way,
Hockey's the passion, over here in Canada. LOL!

 

[scriverb]

Here also in Michigan. Red Wings rock. Mich State Hockey also.

My sons played from 8 yrs. through Juniors here in Michigan. I am a Hockey Dad with frostbitten hands and feet from hanging over big blocks of ice all year long for so many years.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[rugbyguy]

I have tried using the following:

=[Reporting_Period] WHERE Mid(MMYY,3,2) & Left(MMYY,2) Between Format(reports!notifiable_diseases!reporting_period,"yymm") AND Format(reports!notifiable_diseases!reporting_period)

In my control source for the report. Alas, no success.

Advice? where should I be entering this code.

Ps the assumption is correct -it is being store as text (4char)

 

[SkipVought]

So you do NOT have a DATE stored! You have stored 4 numeric characters.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[rugbyguy]

that is correct. I can't really change the type of data that is entered. It is an automated process.

therefor, the reporting_period field is text and 4 characters. This allows me to keep the leading 0's.

 

[SkipVought]

[tt]
Where [Reporting_Period] Between Format(reports!notifiable_diseases!reporting_period,"mmyy") AND Format(reports!notifiable_diseases!reporting_period, "mmyy")
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[rugbyguy]

I apologize Skip,

I guess that I'm not really getting my head around this problem (not to mention I hate having to deal with dates!)

I've entered the code as written in the control for an unbound object in my report, but it gives me an error.

Rugbyguy

 

[SkipVought]

Please post the complete code for one control.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244