query by form, date bug?? 1

orionsong · May 8, 2000

I tried to use a form as a query. Using date as the criteria. You can re-create this problem using northwind.mdb by creating two forms. I am using MS Access 97  SR-2 Problem : Using date, it produces wrong data, or only some of the records. To reproduce the error, I will use the Northwind database as example. We will create 2 forms; frmProb to act as filter and frmResult to see the result of the filter action. frmResult is bound to EMPLOYEES table.   Reproduce the error : 1.  create an unbound form named frmProb create 2 textbox name them fromdate and todate Format : medium date     create a  command button name : cmdFind caption : Find Event Procedure : On Click    Private Sub cmdFind_Click() Dim strWhere As String On Error GoTo Err_cmdFind_Click     strWhere = ("[HireDate] Between #" & Me!fromdate & "# AND #" & Me!todate & "#&quot

DoCmd.OpenForm "frmResult", WhereCondition:=strWhere    Exit_cmdFind_Click:      Exit Sub Err_cmdFind_Click:      MsgBox Err.Description      Resume Exit_cmdFind_Click      End Sub 2. create a form called frmResult     Record Source = Employees     From Field List,     add employee ID, firstname and lastname, and hiredate to the detail 3. Close both forms. 4. Run frmProb, enter 1 March 1992 in fromdate. Enter 1 December 1992 in todate. 5. Click Find. The result is not correct. There is also wrong output when: 1) Values entered in both fromdate and todate are  between 1-4 of any month of same year Example : Fromdate = 1 March 1992 and Todate = 4 September 1992 the correct result should be 3 records, but the result here is 1 record. Employees table show that there are 3 people hired from April to August. I think there would be incorrect output by other combination as well. There is correct output when 1) Value entered in todate is 5 and above, any month, same year 2) value entered in todate has different year from fromdate Please help.....

JimHorton · May 9, 2000

I'm getting the correct results...Leverling, Davolia, and Fuller for both criteria,  I copied your code for the click direct from your post.  This may sound stupid, but you are looking at the record counter at the bottom if you're in single-form view, and not expecting all records to show at once?   Other than that, I can't see what could be the problem...those 3 are the only hires in '92, and all fit within both criteria.  I'm using access 97 sr1. --Jim

orionsong · May 9, 2000

Thanks Jim for letting me know. When i entered 1 March 1992 to 1 December 1992, i am getting nobody as the answer. When i enter 1 April 1992 to 4 June 1992, my result is only Janet Leverling. Also, i plead innocent of just looking at the record counter. Therefore, what can the problem be? Is it only me? Thanks again Jim. Anyone else who has this problem?

orionsong · May 10, 2000

MS Access 97  SR-2 Congratulations to JimHorton for being voted TipMaster of the Week. After reading JimHorton's reply, i poked around and i found out that it is because of the setting in 'Regional Settings' that i am getting these irregular/erroneous results. My regional setting is English(United Kingdom) which is different from JimHorton's which i presume is English(United States). Therefore, you can re-create the erroneous results that i am seeing by changing your: Control Panel>Regional Settings>Regional Settings tab> English(United Kingdom) You can change back to your original settings after that. Now, what do we go about to do from here? It is a major inconvenience and probably against company policy, if i were to ask the company to change every PC in the network's regional settings to United States. After all, my company is in the UK and other applications are dependant on the current settings. Would this issue produce wrong results in other Microsoft Office applications? Does Microsoft Office 2000 have the same issue? Is Microsoft Works in the same dilemma? What about regional settings for other countries?

JimHorton · May 10, 2000

The UK date format is DD/MM/YYYY, where the US format is MM/DD/YYYY.   Access is incorrectly positioning the mm and dd in the me!to/from-date clause--it looks like the short term solution is to force a format in the me!to/fromdate clause. Observations in the debug window (UK settings): ?cdbl(#1/8/92#)  33611  <--Wrong, this is Jan 8, but UK should show Aug 1st, or 33817 ?cdbl(#8-jan-92#)  33611 <--Correct, ?cdbl(#jan-8-92#) 33611 <--Correct also -Jim

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

query by form, date bug?? 1

orionsong

Programmer

JimHorton

Programmer

orionsong

Programmer

orionsong

Programmer

JimHorton

Programmer

Similar threads

Part and Inventory Search

Sponsor