Re: [MS_AccessPros] Searc VBA is bring a Data Mismatch error

 

hi Art

try:
Const conJetDate = "\#mm\/dd\/yyyy\ hh:nn:ss\#"

... out of curiosity, though, why are you worrying about format when they are already in the American format?   ... you should be able to just add # delimiters around the date; Access will implicitely convert it since it is already American format.  Think of it this way: # is for number, and internally, date is stored as a number, but displayed as text.  Therefore, # are used around data that is a date to let Access know to change it to a number.

strWhere = strWhere & "#" & [myDateField] & "#"

respectfully,
crystal
 
~ have an awesome day ~

On 9/4/17 5:09 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] wrote:

I have the following vba code that searches a form which I found on allen browns page:

 

    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
   Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        'strWhere = strWhere & "([DateEntered] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
        strWhere = strWhere & "([DateEntered] >= " & Format(Me.txtStartDate) & ") AND "
    End If
   
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
       ' strWhere = strWhere & "([DateEntered] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
       strWhere = strWhere & "([DateEntered] < " & Format(Me.txtEndDate + 1) & ") AND "
      
    End If

I know its the constant conJetDate but I do not know how to adjust to work with my DateEntered field which stores data as 8/29/2017 3:11:28 PM. I have tried it with the conJetDate and with out and they both fail. Any ideas?

Thank you ,

Art Lorenzini

SD

__._,_.___

---

Posted by: crystal 8 <strive4peace2008@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___