Action Query with Format

[khurram7x]

Hi all, after a long absence. How do I run Access functions, like Format, in Action Query using CurrentDB.Execute?? I'm trying to write the following query with Format function in a Select statement, but I'm coming across few issues.

1st, I'm getting Syntax Error.
2nd, even if i design this query in designer, it is not displaying values as required and showing only date part of the General Date, where the time is 0:00 or Mid Night. I wanna display it as AM/PM format.

3rd, after values are entered in target table, years automatically changes to 1619 for all values. Though results are correct years, when I run SELECT statement before converting it into Action Query.

Attaching screenshots:

CurrentDb.Execute "INSERT INTO tblEmp_WP_Clocked_Time ( SignedIn, SignedOut, MinutesIn, EmpWPID ) " & _
"SELECT Format(tblEmployeeImport.SignedIn, "dd/mm/yyyy hh:nn:ss AM/PM"), Format(tblEmployeeImport.SignedOut, '" & "dd/mm/yyyy hh:nn:ss AM/PM" & "'), tblEmployeeImport.TotalTime, tblEmployee_WorkPack.ID " & _
"FROM (tblEmployeeImport INNER JOIN tblEmployee ON (tblEmployeeImport.LastName = tblEmployee.LastName) AND (tblEmployeeImport.FirstName = tblEmployee.FirstName)) " & _
"INNER JOIN tblEmployee_WorkPack ON tblEmployee.ID = tblEmployee_WorkPack.EmployeeID"

Thanks,
K

 

[CJ_London]

date is a numeric datatype, using the format returns a string. If you are trying to use a string which looks like a date in a query, you need to surround it with #

All you should need is

....
....
SELECT tblEmployeeImport.SignedIn,tblEmployeeImport.SignedOut,....

then format the results as required in your form or report control properties. Don't worry about what it looks like in a table or query

 

[khurram7x]

maybe it is not working for me because I'm importing Text type data from Excel sheet into General Date/Time field in Access. Attaching screenshot.

Screenshot shows that i am trying to concatenate ActivityDate and SignedInTime columns in Excel sheet to a single Date/Time field. End result is a text field.

If I don't concatenate in Excel, import only ActivityDate and SignedInTime column to Access, then concatenate, I've to use 'Format' function. This is why I used 'Format' function in query in my first post on this topic.

Maybe I'm not using correct approach, and I don't have much experience dealing with Date/Time formats. Maybe someone here put me on the right track. I just want end result in General Date/Time format in a single field.

Thank you.

 

[CJ_London]

you can only format numeric data types (number, date, yes/no). you cannot format text data types.

Read up on what a date type looks like - it is a double, the part before the decimal relates to the date and the bit after the time (expressed as the time in seconds divided by number of seconds in a day).

So the formula should just be =E2+F2, then format as required (but would not bother for importing into Access).

But I would also check the real values in columns E and F, you may find they consist of the same value, column E formatted as a date and column F formatted as a time.

 

[khurram7x]

Read up on what a date type looks like - it is a double, the part before the decimal relates to the date and the bit after the time (expressed as the time in seconds divided by number of seconds in a day).

So the formula should just be =E2+F2, then format as required (but would not bother for importing into Access).

But I would also check the real values in columns E and F, you may find they consist of the same value, column E formatted as a date and column F formatted as a time.

Thank you CJ, for clarification on double.

I still could not get it through right properly. I'm attaching the sample Excel file, based on original file I used for exporting.

It'll be great help for now and future, if could be helped in sorting this.

Thanks.

 

[khurram7x]

Thank you CJ, got the problem sorted by the hint u provided and later applied the manual formatting )