COMPARE DATE variable to DATE/TIME FIELD

[rickyfong]

In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion. MANY Thanks!!

 

[jdraw]

Please give an example.
Date/Time is a data type.
It is stored internally as a number.
You can format the field ShortDate, MediumDate....

 

[rickyfong]

For example, I have a variable TEXT233= date (i.e.today "2014-2-19') and want to compare to a table filed, for instance invoicedate, which is yesterday with value I believe something liked "2014-2-18 10:20:30 am". I believe it cannot be directed to do the following statement and make it compare equally:

If text233 = invoicedate then
....
....
endif

Thanks a lot!!

 

[jdraw]

See this info

Also see this for several functions with examples.

 

[DemonDNF]

I am having a similar problem getting my dates handled properly despite reading a bunch of threads here and other forums.

The goal is to get txtScanPart (a string textbox containing a scanned DateTime barcode)
into txtBarcode (a general date textbox to be used in query against DateTime field).

        [B]txtScanPart[/B] = "20" & strScanData                    ' strScanData = "140211234953"
        strDate = "#" & Left([txtScanPart], 4) & "-" & _
                  Mid([txtScanPart], 5, 2) & "-" & _
                  Mid([txtScanPart], 7, 2) & "#"
        strTime = "#" & Mid([txtScanPart], 9, 2) & ":" & _
                  Mid([txtScanPart], 11, 2) & ":" & _
                  Right([txtScanPart], 2) & "#"
        lngDate = CDate(strDate)                ' = "#2014-02-11#"
        lngTime = CDate(strTime)                ' = "#23:49:53#"
 
'        lngDate = DateValue(strDate)
'        lngTime = DateValue(strTime)
'        dateBarcode = DateValue(strDate)
'        timeBarcode = DateValue(strTime)
'        dateBarcode = CDate(strDate)
'        timeBarcode = CDate(strTime)
'        lngDate = Left([txtScanPart], 8)
'        lngTime = Right([txtScanPart], 6)
'        dateBarcode = CDate(Format(lngDate, "YYYYMMDD"))
'        timeBarcode = CDate(DateValue(lngTime))
 
        [B]txtBarcode[/B] = DateValue(dateBarcode) + _
                     DateValue(timeBarcode)                     ' txtBarcode as Longdate textbox

I broke the logic into separate steps for date and time so that I can see what is going on in debugger.

I've tried every thing I can think of and then some except obviously the right one. I can't figure how to get the string into a format the date functions will digest.

Robert

 

[jdraw]

How do you scan the barcode? Where is it stored? What does it look like?

 

[DemonDNF]

How do you scan the barcode?

Saga details here starting at post #7:
http://www.access-programmers.co.uk/forums/showthread.php?t=220916

Short version:
Android phone, TEC-IT free barcode app, bluetooth dongle, Win XP, Serial Port Profile, Access 2002, MScomm32.ocx.

Where is it stored?

Refer to line #1 previous post, received in strScanData as string, but appended "20" to make complete date format.

What does it look like?

Refer to line #1 previous post, comment says strScanData = "140211234953"

After appending "20" = "YYYYMMDDHHMMSS", split into Date and Time to decrease odds of overflow during conversion.

The field to be searched is a DateTime, used NOW() to initialize at record creation.

I just know I'm doing a noob mistake converting the strings into dates. It's obvious from the "type mismatch" errors in debugging. But I haven't found one example that uses both date and time. It's always been one or the other, and the working code was never posted so I'm kinda clueless at this point - more so than usual.

Robert


EDIT: Basically, I make labels for small drawers containing electronic components. I scan the drawer with my phone and the number automatically pops up on the form at this point.

Now I need to format the string into something the query can understand.

 

[spikepl]

You have left out the variable declarations from your code snippet! Show the ENTIRE code.

 

[pbaldy]

I don't really see the end goal, but you're certainly making a mistake here:

lngTime = DateValue(strTime)

as you'd want the TimeValue() function to get a time. Thus if all else is working correctly how about:

txtBarcode = DateValue(dateBarcode) + TimeValue(timeBarcode)

 

[DemonDNF]

I always prefix variables by type. This is whatI used:

  Dim strScanByte As String
  Dim strDate As String
  Dim strTime As String
  Dim lngDate As Long
  Dim lngTime As Long
  Dim dateBarcode As Date
  Dim timeBarcode As Date

Robert

 

[DemonDNF]

I don't really see the end goal, but you're certainly making a mistake here:

lngTime = DateValue(strTime)

as you'd want the TimeValue() function to get a time. Thus if all else is working correctly how about:

txtBarcode = DateValue(dateBarcode) + TimeValue(timeBarcode)

Will change TimeValue, but it's not working for Date either. I can get it into a string, but as soon as I start trying to change it into a date I get mismatch (of course, it's a string). That's why I had tried to convert to number (used longs), but then I had overflow errors.


EDIT: Oh yeah, I had seen this syntax
txtBarcode = DateValue(dateBarcode) + TimeValue(timeBarcode)
suggested in another post, but I don't get that far.

 

[pbaldy]

You don't want the # in the strings. This works:

  Dim txtScanPart             As String
  Dim dateBarcode             As Date
  Dim timeBarcode             As Date
  Dim strDate                 As String
  Dim strTime                 As String

  txtScanPart = "20140211234953"    ' strScanData = "140211234953"
  strDate = Left([txtScanPart], 4) & "-" & _
            Mid([txtScanPart], 5, 2) & "-" & _
            Mid([txtScanPart], 7, 2)
  strTime = Mid([txtScanPart], 9, 2) & ":" & _
            Mid([txtScanPart], 11, 2) & ":" & _
            Right([txtScanPart], 2)

  dateBarcode = DateValue(strDate)
  timeBarcode = TimeValue(strTime)


  Debug.Print dateBarcode + timeBarcode

Edit: the results was:

2/11/2014 11:49:53 PM

 

[jdraw]

Here's some sample code to help you with getting Date and time from a string.

'---------------------------------------------------------------------------------------
' Procedure : Test201402
' Author    : Jack
' Date      : 19/02/2014
' Purpose   : Demo routine to separate a timestamp string into Date and Time where
'   Date and Time are Date data types.
'---------------------------------------------------------------------------------------
'
Sub Test201402()
    Dim Filename As String
    Dim MyDate As Date
    Dim MyTime As Date
    Filename = "20140211234953"
    MyDate = DateSerial(Left(Filename, 4), Mid(Filename, 5, 2), Mid(Filename, 7, 2))
    MyTime = TimeSerial(Mid(Filename, 9, 2), Mid(Filename, 11, 2), Mid(Filename, 13, 2))
    Debug.Print MyDate & "  " & MyTime
End Sub

Here's the output
11/02/2014 11:49:53 PM

Good luck with you project.

OOOps: I see Paul answered while I was typing.

 

[DemonDNF]

Awesome!

Robert