Question convert text to date (1 Viewer)

[vaskoemilov]

Hi, I'm begginer in access and i have the following problem:
In Acc Query i have field with Date/time type containing "16.1.2012 г. 21:00:00"
After separating date and time in two different fields (text type) i want to convert the date "Jan 16 2012", which is text into date/time type to link it to another table.

Tried DateValue(Format(CDate(left(all_bss_pmg_allbsspmg_cell_bhr_kpi.timestamp,11)),"dd\.mm\.yyyy")) AS [Date]
and different other combinations of them but i get errors.

Pls help

 

[jleach]

If the string format resembles a standard date format, then CDate() should convert it. However, the Format function you are wrapping the CDate with always returns a string, so you're doing yourself no favors there.

Also, you are attempting to name a field Date... this is bad (it's a reserved word, and a function). Change that to something like fldDate to get away from the odd errors it will cause.

hth

 

[jleach]

Additionally, an account of the error numbers and messages that you get goes a long ways towards us being able to help.

Cheers,

 

[vaskoemilov]

Hello and thanks,

After your advice i tried

cdate(Left([all_bss_pmg_allbsspmg_cell_bhr_kpi].[timestamp],11)) AS [fldDate]

but i get "Data type mismatch in criteria expression. (Error 3464)"

original field value - "18.12.2011 г. 01:00:00" - date/time
result of Left([all_bss_pmg_allbsspmg_cell_bhr_kpi].[timestamp],11) AS [fldDate] - "Dec 18 2011" - text.

I don't know if the format are correct. Also tried with cdate(left(.....,10)) because i read that 10 is standart offset for date but same result.
BR,
Vasko

 

[jleach]

Change the periods to "/" instead, then convert the left 10 digits (this will be 10 only if all your date formats are ##/##/####, e.g. - 01 instead of 1).

With the date in the string as "18/12/2011", CDate won't throw a type mismatch:

cdate(Replace(Left([all_bss_pmg_allbsspmg_cell_bhr_kpi].[timestamp],10), ".", "/")) AS [fldDate]

See if that works for you.

hth

 

[vaskoemilov]

Hello,

Tried it but i get the same mistake.


When format(Left([all_bss_pmg_allbsspmg_cell_bhr_kpi].[timestamp],10),"dd\.mm\.yyyy") AS [fldDate] I get "18.12.2011" - text value but with cdate in front same mistake.

Thanks,
Vasko

 

[jleach]

See the attached example. The syntax in the query is correct and converts the textual dates to a Date datatype. You will have to adjust so the field corresponds with the field you actually need though (note, the field you reference in the Left() function needs to be present in the query... is it?)

hth

 

[vaskoemilov]

This is the whole query (make table one)


SELECT DISTINCT all_bss_pmg_allbsspmg_cell_bhr_kpi.timestamp, all_bss_pmg_allbsspmg_cell_bhr_kpi.cell, all_bss_pmg_allbsspmg_cell_bhr_kpi.[TCH Congestion nom], all_bss_pmg_allbsspmg_cell_bhr_kpi.[SDCCH Congestion nom], all_bss_pmg_allbsspmg_cell_bhr_kpi.[Total Traffic,Erl], all_bss_pmg_allbsspmg_cell_bhr_kpi.[HR Traffic,Erl], all_bss_pmg_allbsspmg_cell_bhr_kpi.[FR Traffic,Erl], all_bss_pmg_allbsspmg_cell_bhr_kpi.[TCH Availability,%], format(Left([all_bss_pmg_allbsspmg_cell_bhr_kpi].[timestamp],10),"dd\.mm\.yyyy") AS fldDate, Right(all_bss_pmg_allbsspmg_cell_bhr_kpi.timestamp,8) AS [Busy Hour] INTO GSM_BH_KPI
FROM all_bss_pmg_allbsspmg_cell_bhr_kpi
WHERE (((all_bss_pmg_allbsspmg_cell_bhr_kpi.timestamp)>Date()-30));

I saw the example is working and i will try it again.
Thanks for everything,
Vasko

 

[jleach]

Looking at your SQL, I would further suggest a lot of field name changing. For instance, special characters (commas, percent signs, etc) tend to cause lots of problems (forget that MS says it's supported with square brackets... not quite). Also, names in spaces are generally not considered good practice either... use CamelCaseFieldNames instead of spaces.

Between that and reserved words, I'll wind up prefixing "fld" to fields, and then I have fields like:

fldName, fldAvailabilityPercent, fldDate etc etc etc.

same thing with tables, prefix them with "tbl", get rid of the underscores and use CamelCase instead. Forms, reports, controls etc as well.

Aside from being much less error prone, your code will be a LOT easier for yourself and others to read.

Cheers,

 

[vaskoemilov]

Hello,

Thank you a lot for the advices given. I'll try to keep up with all of them. The reason here for all the odd names is that they are part of a DB which I can't mess with
As for the problem i had - solved on the next step.
The make table query always showed mismatch mistake but after making the table in the next query based on the table i was able to Cdate the field fldDate. Maybe extra step but it is working

Thanks again for everything!
Best Regards,
Vasko

P.S. Mean while I think i got where the problem is:
In the field I'm trying to separate there are dates "1.1.2012 г. 00:00:00" so when I left(.....,10) the field I get "1.1.2012 г" as string so Cdate can't recognise the date format I think. Fixed with Cdate(Int(.....)).

 

[jleach]

P.S. Mean while I think i got where the problem is:
In the field I'm trying to separate there are dates "1.1.2012 г. 00:00:00" so when I left(.....,10) the field I get "1.1.2012 г" as string so Cdate can't recognise the date format I think. Fixed with Cdate(Int(.....)).

Yea, that type of stuff is definately a problem (1.1.20012 vs. 01.01.2012). One of the reasons that hardcoding anything (even the number of characters to get, such as 10), isn't always the best idea.

That said, you can get the position of the first space by using InStr(), but it's yet another function call, and depending on the number of records you might see a performance hit.

Myself, when I start getting into functions wrapping functions wrapping functions in an SQL to get the data I need at the end, I'll consider writing a public function to do it and make one call to the function:

Option Compare Database
Option Explicit

Public Function GetADateFromThis(ByVal s As String) As Date

  Dim i As Integer

  'find the first space
  i = InStr(1, s, " ")

  'get just up to the first space
  s = Left(s, i)

  'convert the "." to "/"
  Replace(s, ".", "/")

  'return a date
  GetADateFromThis = CDate(s)
End Function

The your query just calls the function: GetADateFromThis([yourtable].[yourfield]) As fldDate

It's a lot easier to read on the SQL side, a lot easier to manage debugging ect because we're using VBA now, but it's also not quite as fast.

Required performance is really the big driver between whether or not you want to save yourself a headache trying to look at five or six functions crammed into an SQL string or one UDF call.

Cheers,

 

[mac.vba]

Hi jleach,

even I had an issue. I have date in the format e.g "Jan-23-2012 03:45:59 PM" in the column. please suggest a syntax to fill in the Built option in design mode of the query.

I want output as 01/23/2012 in one column & another column should have 03:45:59 as time given above.

Please reply at the earliest. The file which you have attachec above was so helpful to me, but for this kinda format, it doesn't work.

Thanks you.

 

[Beetle]

If all the text values in your table are like your example, Access should have no problem recognizing them as Date/Time values.

DateValue("Jan-23-2012 03:45:59 PM") returns 1/23/2012

TimeValue("Jan-23-2012 03:45:59 PM") returns 3:45:59 PM