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

 

[rugbyguy]

Here is what is entered in the control in my report.

=Where([Reporting_Period] Between Format(Reports!notifiable_diseases!reporting_period,"mmyy") And Format(Reports!notifiable_diseases!reporting_period,"mmyy"))

I also have the following criteria in my query [notifiable_diseases]:
>=[Please Enter Report Start date MMYY] And <=[Please Enter Report End Date MMYY]

 

[SkipVought]

is Reports!notifiable_diseases!reporting_period a REAL DATE?

You CANNOT enter a REAL DATE with ONLY Month & Year! You also need DAY.

So you would have to take Start Date and End Date and PARSE each into Mo and Yr
[tt]
A_RealStartDate = DateSerial("20"&Right([Start],2), Left([Start],2), 1)
[/tt]


Skip,

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

 

[jksmi]

Hi all,

just a thought but if you enter a start date of "0104" you'll get "0204" but you'll also get records from "0203" and "0202" etc...

I think you might need to revisit scriverbs suggestion and convert every thing to serial date in your where clause, i.e. in your query the where clause would look something like...

WHERE DateSerial(CInt(RIGHT(REPORTING_PERIOD, 2)), CInt(LEFT(REPORTING_PERIOD, 2)), 2)
    BETWEEN DateSerial(CInt(RIGHT([Please Enter Report Start date  MMYY], 2)), CInt(LEFT([Please Enter Report Start date  MMYY], 2)), 1)
        AND DateSerial(CInt(RIGHT([Please Enter Report End Date MMYY], 2)), CInt(LEFT([Please Enter Report End Date MMYY], 2)), 3)

open a copy of your query, notifiable_diseases, in design mode and go to View and select SQL, just post the above over your existing WHERE clause. Run the query and see if it gives you anything like the results you expected (I'm assuming that REPORTING_PERIOD is a valid field in this query and I haven't made any typo's)...

HTH, Jamie
FAQ219-2884

 

[KenReay]

Hi Please see my earlier post, it will allow you to select records as you required, provided your "dates" do not span the change of first two digits of year (ie 1999-2000),



Regards

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

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

UK

 

[SkipVought]

Jamie,

You are ABSOLUTELY CORRECT!

Actually, it is foolish NOT store DATES as DATES! The designer was probably a mainframe programmer that made at least TWO other mistakes...

1) Shortened the YEAR (Oh, Y2K!)

2) Placed Month BEFORE Year.


Skip,

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

 

[KenReay]

Hi

We seem to have lost the plot here

from original post:

"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?"

my suggestion from several posts back:

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

 

[jksmi]

Hi Skip,

I can sympathise with RugbyGuy as dates can be a right mare (blind ones and SQL ones), especially if your regional settings are UK - being ddmmyyyy this can play havoc with your SQL - so I can see why it must have been tempting to set it to a text field. I always end up using date fields with long date format now so I can quickly see when I'm heading for date troubles (SQL equivalent of a red carnation I guess)...

HTH, Jamie
FAQ219-2884

 

[jksmi]

Hi Ken,

just curious - why would you suggest the mid function rather than right? is it in case someone types 5 character or another reason?

HTH, Jamie
FAQ219-2884

 

[KenReay]

Hi

No sensible reason, I just tend to use Mid()

Don't mean to be rude but..

can we can these little discussions and just tell me does it work!

Regards

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

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

UK

 

[rugbyguy]

I've used this code in my parametre in the query but I don't think that I've got the syntax quite right....

>=[Please Enter Report Start date MMYY] WHERE Mid(MMYY,3,2) & Left(MMYY,2) Between Format( [Reporting_Period] ,"yymm") AND Format(reporting_period!ToDate) And <=[Please Enter Report End Date MMYY] WHERE Mid(MMYY,3,2) & Left(MMYY,2) Between Format( [Reporting_Period] ,"yymm") AND Format(reporting_period!ToDate)

I didn't set out to have this many posts - perhaps a donation is in the offing!

 

[SkipVought]

This recent discussion has still not adddressed the sort sequence issue of MMYY
[tt]
0104
0203
0303
0403
0404
[/tt]
Where start>='0104' AND end <= '0404'

The DATA is fundamentally flawed.

Skip,

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

 

[BobJacksonNCI]

Skip,

You get extra points for tenacity and attention to detail!

Spartan Bob got worn down...

So, to make "Between" work with dates, the table entries have to be converted to dates. I'm not clear why Spartan Bob included CIng, so how about asking the Users to type in MM/DD/YY, always specifying DD as 01. Then:

DateSerial(Right(MMYY,2),Left(MMYY,2),1) Between [Please Enter Report Start date  MM/DD/YY] and [Please Enter Report End date  MM/DD/YY]

To finish debugging, start with the least amount of code to get a result - leave off reporting period stuff until you're selecting the desired records.

Bob (of the Hawkeye variety - graphics pending)

HTH,
Bob

 

[rugbyguy]

Hi Bob,

I had initially tried something similart to what you suggested and I got "expression too complicated" error.

I've tried the code that you gave me and got the same answer.

thanks,
Rugbyguy (AKA Rob)

 

[BobJacksonNCI]

Hmmm,

Maybe it WOULD be easier to convert the table field to datetime...

O.K., I built a table tblDtSerial and put in entries from 0103 through 0604. Using the following query, if you type in dates to the prompts in MM/01/YYYY (include slashes and the century, the following works:

SELECT tblDtSerial.MMYY, DateSerial(Right([MMYY],2),Left([MMYY],2),1) AS dtSerial
FROM tblDtSerial
WHERE (((DateSerial(Right([MMYY],2),Left([MMYY],2),1)) Between DateValue([Please Enter Report Start date  MM/DD/YYYY]) And DateValue([Please Enter Report End date  MM/DD/YYYY])));

HTH,
Bob

 

[SkipVought]

This will work...
[tt]
WHERE (right([Start date],2) & left([Start date],2) Between '0401' And '0404')
[/tt]
this puts the CRITERIA SEQUENCE in YYMM format.

Skip,

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

 

[BobJacksonNCI]

Skip,

It does work, but requires the User to select by YYMM instead of MMYY. That may be better than my solution requiring MM/DD/YYYY, slashes included, but both require the User to enter the date parameters differently than they do today.

rugbyguy,

Are you SURE you don't want to convert the field to date type?



HTH,
Bob

 

[SkipVought]

What the USER enters does not need to be changed. MONTH & YEAR can be manipulated very simply in code.

I agree that the best LONG-TERM solution is to convert the pseudo-date to REAL DATE. But remember, to convert, you need to assign year, month & DAY.

Skip,

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

 

[KenReay]

Hi

Isn't that essentially the same as saying:

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


Regards

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

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

UK

 

[SkipVought]

Ken,

All it is, is a 4 character string!

AND what the user is entering is NOT A DATE so WHY the FORMAT FUNCTION???

Skip,

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

 

[BobJacksonNCI]

Ken,

Absotively. However, it's no longer clear whether the date parameters come from forms or prompts. I was under the impression the Users are seeing the prompt [Please Enter Report Start date MMYY]. Certainly the format of the entry can be manipulated internally if the entry is on a form, not so easy when prompted for four digits.

So, from an assistance viewpoint, we're guessing whether it's more important to rugbyguy to resolve with minimal change or to recommend design change - put the dates on a form. And, if the recommendation is to change the design, then the best design would be to change the field to a date.

The missing piece is knowing if this is a new feature that can be tinkered with before roll-out or if it's already seen by the Users and therefore change may be significant.

Bob - no flames intended