Convert text field to date field (1 Viewer)

rooley

New member
Local time
Today, 00:09
Joined
Jan 23, 2024
Messages
7
After searching through the treads on this topic, I needed help finding a post that addressed this question clearly.

I receive a .cvs file every morning from my HVAC system controller that gives me time-specific readings of all the mechanical equipment in the building. There are generally 6,000 records that look like (/SBMA/ArtStorage105_temp,14-Oct-23 8:45 AM PDT,68.5)---First Field: location of the sensor, Second Field: date and time, Third Field: value.

These readings are taken every 15 minutes for both temperature and humidity. These records are imported to Access as text; the only field that makes sense is the sensor location. The other two fields must be converted to date and number.

I have tried to convert the data on import, but that only deletes the data once imported. I have tried a few update functions but the PDT gets in the way...

SO, I turn to the forum for help.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:09
Joined
Sep 21, 2011
Messages
14,448
Code:
? cdate("4-Oct-23 8:45 AM")
04/10/2023 08:45:00

? cdate(Replace("4-Oct-23 8:45 AM PDT","PDT",""))
04/10/2023 08:45:00
 

Mike Krailo

Well-known member
Local time
Today, 03:09
Joined
Mar 28, 2020
Messages
1,044
That almost works Gasman, but this gets rid of the PDT part.
Code:
cdate(replace("4-Oct-23 8:45 AM PDT"," PDT","",1))
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:09
Joined
Sep 21, 2011
Messages
14,448
That almost works Gasman, but this gets rid of the PDT part.
Code:
cdate(replace("4-Oct-23 8:45 AM PDT"," PDT","",1))
That was the intention Mike, as the CDate will not work with that in?

I have tried a few update functions but the PDT gets in the way.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:09
Joined
Feb 19, 2002
Messages
43,484
You might want to add the time zone to a separate column or possibly even use it to convert the time to GMT for all locations depending on what you need to do with the time values. It won't make much sense to have dates from different time zones all being processed together.
 

rooley

New member
Local time
Today, 00:09
Joined
Jan 23, 2024
Messages
7
You might want to add the time zone to a separate column or possibly even use it to convert the time to GMT for all locations depending on what you need to do with the time values. It won't make much sense to have dates from different time zones all being processed together.
Time Zone is not important as I am only concerned with one facility.....if I have multiple facilities in multiple countries.....maybe.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:09
Joined
May 21, 2018
Messages
8,605
You can get the type mismatch if you get a string that cannot be converted to a date. In this case if you had another time zone PDT, EST, MT, CT etc this will fail. Do all dates have PDT or do some have something else? If they have something else this would fail.
Can you paste a list of dates?
 

rooley

New member
Local time
Today, 00:09
Joined
Jan 23, 2024
Messages
7
You can get the type mismatch if you get a string that cannot be converted to a date. In this case if you had another time zone PDT, EST, MT, CT etc this will fail. Do all dates have PDT or do some have something else? If they have something else this would fail.
Can you paste a list of dates?
All records have the following structure.
(/SBMA/ArtStorage105_temp,14-Oct-23 8:45 AM PDT,68.5)

Sensor Zone, Date of Reading, Value of Reading

Sensor Zone has two entries per reading time....TEMP and RH. Readings for each are taken every 15 minutes.

I don't care about the PDT....as all of my sensors are located in a single building.
 

Mike Krailo

Well-known member
Local time
Today, 03:09
Joined
Mar 28, 2020
Messages
1,044
What MajP said is true. I assumed you had dates like you showed in your example. You could just create a function that converts every possible time zone or likely time zone into a proper date without the time zone. Use the instr function to test for the presence of a particular timezone.

So you need to either scan through your date data and make sure there is no other timezone string in it, or create a function to account for all the likely timezones that would appear in the data.
 

plog

Banishment Pending
Local time
Today, 02:09
Joined
May 11, 2011
Messages
11,669
Validate data before converting and updating it:

Code:
SELECT [Date of Reading], Replace("[Date of Reading]"," PDT","",1)) AS DateString
FROM YourTableNameHere
WHERE IsDate(replace("[Date of Reading]"," PDT","",1)) = False

If you have any "dates" that aren't dates that query will show them. Fix them then run your UPDATE query. I suggest you validate the field you are converting to a number as well.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:09
Joined
May 21, 2018
Messages
8,605
I make a public function for this.
Code:
Public Function GetDateFromString(ByVal varDate As Variant) As Variant
  Dim aDate As String

  If Not IsNull(varDate) Then
    varDate = Trim(varDate)
    varDate = Trim(Split(varDate, ",")(1))
    varDate = Trim(Split(varDate, ",")(0))
    If InStr(varDate, " AM ") > 0 Then
      varDate = Left(varDate, InStr(varDate, " AM "))
    ElseIf InStr(varDate, " PM ") > 0 Then
      varDate = Left(varDate, InStr(varDate, " PM "))
    End If
    If IsDate(varDate) Then GetDateFromString = CDate(varDate)
  End If
End Function

Public Sub testGetDate()
  Dim x As String
  x = "/SBMA/ArtStorage105_temp,14-Oct-23 8:45 AM PDT,68.5"
  Debug.Print GetDateFromString(x)
  x = "/SBMA/ArtStorage105_temp456, 16-Nov-23 12:45 PM PST,78.5"
  Debug.Print GetDateFromString(x)


End Sub

A better function would be regular expressions, but this works on the example data and permutations of it. Assumes commas
sensorzone comma datetime comma reading
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:09
Joined
May 7, 2009
Messages
19,246
using query to update the [DateField]:
Code:
Update yourTable Set [DateField] = CDate(Left(Mid([TextField],Instr(1,[TextField],",")+1), Instr(1,Mid([TextField],Instr(1,[TextField],",")+1), "PDT")-1))
 

Users who are viewing this thread

Top Bottom