Syntax for dates in vb

WineSnob

Not Bright but TENACIOUS
Local time
Today, 16:12
Joined
Aug 9, 2010
Messages
211
I am building a dynamic filter in code and am trying set the date range for a query. The text fields work fine. I am having trouble with the code for date range. I think I am missing "#" sign to pass to the query. Here is what I have. Any idea how where to put the # ?
Thanks.

' Check for Date Range
If Me.txtStartDate > "" Then
varWhere = varWhere & "[NewWOdate] Between " & Me.txtStartDate & " AND " & Me.txtEndDate
End If
 
Hello..
Try this way..

Code:
dim sdate,edate as integer
 sdate = clng(cdate(Me.txtStartDate))
 edate = clng(cdate(Me.txtEndDate))
varWhere = varWhere & "[NewWOdate] Between " & sdate & " and " & edate
 
Suggestion:
' Check for Date Range
dim varWhere as string
If IsDate(Me.txtStartDate) Then
varWhere = varWhere & "[NewWOdate] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
End If

Also, in your development code, use Debug.print varWhere or any other variable where you are unsure of syntax etc. This will show you the contents of variables that are being passed. You can always comment them out later, but it solves a lot of syntax issues, or totally unexpected strings during development.

Good luck
 
I get a type mismatch error when opening the form
sdate = clng(cdate(Me.txtStartDate))


Code:
dim sdate,edate as integer
 sdate = clng(cdate(Me.txtStartDate))
 edate = clng(cdate(Me.txtEndDate))
varWhere = varWhere & "[NewWOdate] Between " & sdate & " and " & edate
 
dim sdate,edate as integer

This doesn't do what the author thought.
sDate will be a variant
eDate will be an integer


clng( with today's date ) will give 40786 which is larger than an integer can store(32767)

You could try
Dim sdate as long
Dim edate as Long
 
This doesn't do what the author thought.
sDate will be a variant
eDate will be an integer


clng( with today's date ) will give 40786 which is larger than an integer can store(32767)

You could try
Dim sdate as long
Dim edate as Long

inattention.. :cool:

Thanks for the correction.. ;)
 
Thanks all. JDraw suggestion worked. Now a new issue (see below)
' Check for Date Range
dim varWhere as string
If IsDate(Me.txtStartDate) Then
varWhere = varWhere & "[NewWOdate] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
End If

I am also adding a search feature where the variant may be in one of two fields either in [NewWOno] or in [oldWOno]. I am trying work out the "OR" feature. Here is my attempt that doesn't work.

' Check for LIKE WorkOrder
If Me.txtWOno > "" Then
varWhere = varWhere & "[NewWOno] LIKE """ & Me.txtWOno & "" Or "[oldWOno] LIKE """ & Me.txtWOno & """ And "
End If
 
I think this way work..

varWhere = varWhere & "[NewWOno] " & "Like '" & Me.txtWOno & "'" & " or [oldWOno] " & " Like '" & Me.txtWOno & "'" & " And "

I hope you think of the right.. ;)
 
Thanks to all. Taruz solution works. I just cannot figure out the ' vs " vs """. Makes my head hurt.
 

Users who are viewing this thread

Back
Top Bottom