If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Query in VBA and date range

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 30
Query in VBA and date range

Hi all,

I tried this query statement in VBA but in return no record.

Dim dtDate1 As Date
Dim dtDate2 As Date
dtDate1 = Trim(Me.txtClosing1)
dtDate2 = Trim(Me.txtClosing2)

Set rs1 = dbs.OpenRecordset("SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" _
& " and ([eff date]>= " & dtDate1 & " and [eff date]<=" & dtDate2 & ")")

[eff date] is in datetime MS access format.

This thing really made me confuse, as i see no reason for it to return 0 record. Please advice if you find anything wrong with my query.
Thanks.
Reply With Quote
  #2 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Hi

Three things:
1) Check out BETWEEN in help (passe Mike (passe Izy))
2) You have enclosed your text with single quotes. You need to use hashes (or pound signs if you are from the US) i.e. # for dates.
3) Are you working in MM/DD/YYYY format?

HTH
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: Ger
Posts: 1,969
Try

" AND ([eff date]>= #" & dtDate1 & "# and [eff date]<=#" & dtDate2 & "#)")
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 30
Hi Hammbakka and HTH,

Thanks for the reply. Aprreciate it.
1) I've tried "BETWEEN"
2) I'm from Asia and my date format is in dd/mm/yyyy
3) Have tried using #

Still returning 0 records.

Any more ideas?

Thanks
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Location: Southampton, UK
Posts: 368
Run a debug statement:

Code:
debug.print "SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" & " and ([eff date]>= " & dtDate1 & " and [eff date]<=" & dtDate2 & ")"
Check that the output looks as you would expect. Run the output (copy/paste) as standalone SQL to check it's giving you the desired results. If it still gives 0 records then I think you have no records meeting the criteria.

Chris
Reply With Quote
  #6 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Hi

Howey's idea is always a good one though remember to insert the #s!

VBA is a bit US centric. When you concatenate dates into a string it changes them to MM/DD/YYYY where ever possible (e.g. 31/01/2005 remains as is since it cannot be a MM/DD/YYYY date but 04/01/2005 is changed to 01/04/2005) irrespective of your local date\ time settings.

Personally I would do something like:
Code:
Dim dtDate1 As String
Dim dtDate2 As String

dtDate1 = Trim(Me.txtClosing1)
dtDate2 = Trim(Me.txtClosing2)

IF Not ISDATE(dtDate1) OR Not ISDATE(dtDate2) THEN
'Not two dates. Give some message
ELSE
Set rs1 = dbs.OpenRecordset("SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" _
& " and [eff date] BETWEEN #" & dtDate1 & "# and #" & dtDate2 & "#")
END IF
HTH (HTH = Hope this helps )
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 30
Thanks all. Really fruitfull discussion.

I manage to get the results i want.

I just retype everything and swap the condition (where date between date1 and date 2 and transport co = cmbtransporter.

I dont know why, but i guess, it's Window.

Thanks mates...
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jun 2006
Posts: 103
how about "SELECT * FROM table where dateExample BETWEEN DateValue('" & startDate & "') and DateValue('" & endDate & "')"

DateValue('dateString') will make a date string of "05/05/2006") cast into a number of 3#### (sorry, i cant remember the number of this date example, to make the date a number so you can use between command.

Hope this helps
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Posts: 30
Definately will try on that. Thanks
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On