Between Format Date and Format Date issue

[NumbersMax]

All,

I'm running Access 2003/Window XP. I have a query that I run every Monday that has suddenly stopped running. I've narrowed the problem to the date criteria. I'm converting the date-formatted begin and end dates using Format() to text like "yyyymmdd" so that it matches the date field in our mainframe. This query and many others like it have been working for over a year now. The criteria looks like this:

Between Format([Forms]![Main Form]![BeginDate],"yyyymmdd") And Format([Forms]![Main Form]![EndDate],"yyyymmdd")

Let me emphasize that NO changes have been made to the form date fields or query design. The one major change I can think of is the set up Windows Updates I ran before I left work on Friday.

The reason I believe it could be the windows updates is because I tried to use the very same Between, Format, And statements on my computer to help someone else a week or so ago...it ran on my computer fine, but refused to run on his.

Can anyone confirm whether a windows update could cause this problem? Does anyone know of a possible correction to the problem that won't require finding/rewriting all the queries I have like this to ask for the date already in the text field...I already know one way to make that work:

Between [enter begin date like yyyymmdd] And [enter end date like yyyymmdd]

Thanks in advance for your advice,
Max

 

[SydneyGeek]

Max, sometimes an update screws up a library reference... worth a look.
Alt+F11, Tools > References and look for missing refs. if any are found, deselect and find the corresponding version down the list. Select, click OK and try again.

Denis

 

[NumbersMax]

Denis,

I've learned a lot from you and others on this site, but this is one of those things that I haven't learned yet. I wouldn't know a missing reference if it bit me on the ankle! I will include a list below of the references that are checked. Could you have a look and see if anything strikes you as unusual or missing?

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Excel 11.0 Object Library
Microsoft ActiveX Data Objects Recordset 2.8 Library
Microsoft Office 11.0 Object Library
Microsoft Word 11.0 Object Library

Thanks for your time,
Max

 

[SydneyGeek]

Hi Max,

They all look like valid libraries but do any of them have the word <MISSING><MISSING> to the left of them? That's what I was referring to. If none do, it's another issue.

Denis

 

[NumbersMax]

No, none of them have <Missing> in front of them.

 

[SydneyGeek]

Have you tried leaving out the Format function? Why is it necessary -- are the dates in the mainframe text instead of dates?

Have a go at...

Between CLng([Forms]![Main Form]![BeginDate]) And CLng([Forms]![Main Form]![EndDate])

Denis

 

[Will_B]

You mentioned your YYYYMMDD format is "so that it matches the date field in our mainframe." Have any changes been made to that mainframe or the processes that get its data into your Access database? For example, is the mainframe data imported into a datamart using SQL Server DTS packages (or whatever the latest version of SQL Server calls it now) --- and perhaps the DTS package was recently edited? Apologies if this was a REALLY obvious question you already considered.

 

[NumbersMax]

Denis/Will,

Thank you for your help. The dates in the mainframe are text, unfortunately. I'm afraid I am not sure how to describe the situation, other than to say the data table from the mainframe is linked into Access. We are in the process of switching to an enterprise system from an old IBM based CICS system and an HP production management system. I guarantee that they wouldn't change the formats of the old system's fields...they just don't want to spend any time on those at all.

I recently learned that we are having issues with the mainframe in question. Something happened, that I'm not privy to, and when the issue came up, they discovered that Microsoft Access queries were the only way of noticing the problem. The tables had data in them, but when queried, would show mostly "#Deleted" in the fields. Strangely, if a report was created based on the query, you could get the correct information on the report printout. Crystal Reports created from the same tables had no issues.

On Friday, they installed some sort of fix (which I'm also not privy to) that seems to have corrected the problem...very strange indeed.

Thanks again.
Max

 

[SydneyGeek]

Glad to hear it's sorted Max,

And no wonder we were fishing if it was all happening upstream of Access.

Denis