Simple Query why not working?

[nval005]

Basic QUery with Parameter taken from text field
Hello guys,

I've done this many times but somehow this one does not work...am I going insane?


Dim sql01, sql02 As String
sql01 = "SELECT * FROM CPOrganisation "
sql01 = sql01 & "WHERE (((CPOrganisation.DateEntered) Between #" & Me.txtBatchStartDate.Value
sql01 = sql01 & "# and #" & Me.txtBatchEndDate.Value & "#));"
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(sql01)


if date i chose is 8/04/2010 (start date) and 8/04/2010 (end date) it produce nothing (MsgBox rst.RecordCount) I get zero

but in the QUery

SELECT *
FROM CPOrganisation
WHERE (((CPOrganisation.DateEntered) Between [Forms]![frmMain]![txtBatchStartDate] And [Forms]![frmMain]![txtBatchEndDate]));


it gives me the correct number (10 records enter for the date: 08/04/2010)


Anyone can shed light on this one? Thanks a lot guys!

Happy weekend everybody!!!

 

[DCrake]

Did you test for Not rst.EOF and Not rst.BOF ?

Also did you issue a rst.MoveLast before the rst.RecordCount ?

What format is the date field set as in your table?

Could be that if it is set as general or no formatting there may be a time element that is influencing the results.

 

[nval005]

thanks for quick reply, heres more of my code

'-------for counting the records
sql01 = "SELECT * FROM CPOrganisation "
sql01 = sql01 & "WHERE (((CPOrganisation.DateEntered) Between #" & Me.txtBatchStartDate.Value
sql01 = sql01 & "# and #" & Me.txtBatchEndDate.Value & "#));"
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(sql01)
'break if RST = 0 , meaning no record!!!
'rst.MoveLast
DoCmd.OpenQuery "qryBatchUpload"
If rst.RecordCount = 0 Then
MsgBox "No record(s) to export. Check the date please."
Exit Sub
Else
rst.MoveLast
End If



i also open the query that takes parameter from the same field...im going crazy with this 1...

 

[nval005]

What format is the date field set as in your table?
= was in date format, changed it to text, no go


Could be that if it is set as general or no formatting there may be a time element that is influencing the results.
= the textfield only has date value on it, no time included, but why is the query that takes the value from the same field works as well? soooo weird, maybe i missed something obvoius? been coding the whole day...


heres another one , 10 records entered on 08/04/2010 , but if i make start field a day or so and end date a day or so, it works fine .... ??

 

[DCrake]

Try the

Set rst = CurrentDb.OpenRecordset(sql01)

If Not rst.EOF And Not rst.BOF Then

    rst.MoveLast
    Debug.print rst.RecordCount

End If

Also you cannot open select queries to count records.

 

[Galaxiom]

If your txtBatchStartDate and txtBatchEndDate field is not MM/DD/YYYY the query will not work.

 

[nval005]

nothing on debug window

 

[DCrake]

When I referred to the formatting of your date field I meant the date type format the field type. As the previous post suggests it may be that is is not translating the dates correctly between dd/mm/yyyy and mm/dd/yyyy

 

[nval005]

aha!!!!!!! tnx i shall check that....

 

[DCrake]

Try doing a debug.print on the sql string to see how it is being read. To prove it copy the sql directly into a new query and view results. Access may be reversing the formatting.

 

[nval005]

hmm still nothing

 

[nval005]

hello Drake,

maybe you are right, I hard coded the date in the sql01 and it works fine... I'll try that. thanks.

 

[DCrake]

Can you post a copy of the mdb?

 

[nval005]

If Me.txtBatchEndDate.Value = "dd-mm-yyyy" Or Me.txtBatchStartDate = "dd-mm-yyyy" Then
MsgBox "The date field is empty, please enter start and end date"
Exit Sub
Else ' carry on
End If
MsgBox Me.txtBatchStartDate.Value & " --- " & Me.txtBatchEndDate.Value
'-------for counting the records
Dim stdate, enddate As String
stdate = Format(CDate(Me.txtBatchStartDate.Value), "DD/MM/YYYY")
enddate = Format(CDate(Me.txtBatchEndDate.Value), "DD/MM/YYYY")

'MsgBox stdate & " = " & enddate
sql01 = "SELECT * FROM CPOrganisation "
sql01 = sql01 & "WHERE (((CPOrganisation.DateEntered) Between #" & stdate
sql01 = sql01 & "# and #" & enddate & "#));"
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(sql01)
'break if RST = 0 , meaning no record!!!
'rst.MoveLast
DoCmd.OpenQuery "qryNSSBatchUpload"
If rst.RecordCount = 0 Then
MsgBox "No record(s) to export. Check the date please."
Exit Sub
Else
rst.MoveLast
End If


Set rst = CurrentDb.OpenRecordset(sql01)

If Not rst.EOF And Not rst.BOF Then

rst.MoveLast
Debug.Print rst.RecordCount
' MsgBox rst.RecordCount


End If





------------------------------

DoCmd.OpenQuery "qryNSSBatchUpload" this one takes the date (start and end ) from the same text field and it produce the desired results

the sql01 string that takes the SQL , I did a msgbox sql01 and got this

SELECT * FROM CPOrg WHERE (((CPOrg.DateEntered) Between #08/04/2010# and #08/04/2010#));

which is exactly similar the the query I made manually for testing...

 

[nval005]

how do u attach it here? sorry im a newbie in this forum.

 

[DCrake]

As mentioned you cannot use DoCmd.Openquery on a query that is not an action query (Update,Delete,Etc)

Take a look at this sample database on how to use dates between forms and queries. It may help.

 

[nval005]

oh here you go

sorry for the errors, its a stripped down version.

 

[nval005]

DoCmd open query I put that in there for testing purpose only

 

[DCrake]

Your date field is set as a text field.

Change to Date/Time type

format :dd/mm/yyyy
input mask :99/99/0000;0;_

 

[nval005]

hi Drake,

i've found the error.......

in the database it was

8 /04/2010

somehow there's a stupid space after 8 ...


Thanks for the help!!!