Convert Date

[D Willett]

Not sure if this is the correct forum, I'm working with MS Query through my spreadsheet so presume it should be ok.
A couple of questions, Ms Query, are the expressions the same as with MS Access?

I also need to remove the time element from [JobCreationDate] from the following SQL: ( Forgot to say I want the query to pull data either from two cells [startdate] [enddate] or just the current month.

Code: Select all

SELECT JOB_CONTROL_VIEW.JobID, JOB_CONTROL_VIEW.VehicleReg, JOB_CONTROL_VIEW.VehicleMake, JOB_CONTROL_VIEW.VehicleModel, JOB_CONTROL_VIEW.EstimatorCode, JOB_CONTROL_VIEW.JobCreationDate, JOB_CONTROL_VIEW.JobAmendDate, JOB_CONTROL_VIEW.DiscountParts, JOB_CONTROL_VIEW.DiscountLabour, JOB_CONTROL_VIEW.DiscountMaterials, JOB_CONTROL_VIEW.DiscountSpecialist, JOB_CONTROL_VIEW.DiscountOverall, JOB_CONTROL_VIEW.EstLabourHours, JOB_CONTROL_VIEW.EstLabourNet, JOB_CONTROL_VIEW.EstMaterialNet, JOB_CONTROL_VIEW.EstPartsNet, JOB_CONTROL_VIEW.EstSpecialistNet, JOB_CONTROL_VIEW.EstTotalNet, JOB_CONTROL_VIEW.EstBottomLineDiscAmount, JOB_CONTROL_VIEW.JobStatus, JOB_CONTROL_VIEW.Notification_DateTime, JOB_CONTROL_VIEW.Vehicle_WriteOff, JOB_CONTROL_VIEW.Work_Provider, JOB_CONTROL_VIEW.JobDateOutExpected, JOB_CONTROL_VIEW.JobCurrentLocation, JOB_CONTROL_VIEW.CompletionDate, JOB_CONTROL_VIEW.Insured, JOB_CONTROL_VIEW.CPExplicitStartDate, JOB_CONTROL_VIEW.OrigExpCompDate, JOB_CONTROL_VIEW.ImportedLabourHours, JOB_CONTROL_VIEW.HoursRemaining, JOB_CONTROL_VIEW.EstimatedDate, JOB_CONTROL_VIEW.AuthorisedDate, JOB_CONTROL_VIEW.InProgressDate, JOB_CONTROL_VIEW.InvoicedDate, JOB_CONTROL_VIEW.WriteOffDate, JOB_CONTROL_VIEW.Insurer, JOB_CONTROL_VIEW.JobType, JOB_CONTROL_VIEW.LabourRate, JOB_CONTROL_VIEW.AllocHours, JOB_CONTROL_VIEW.ProdHoursBooked, JOB_CONTROL_VIEW.ActualHoursBooked, JOB_CONTROL_VIEW.Collection_DateTime, JOB_CONTROL_VIEW.Recovery_DateTime, JOB_CONTROL_VIEW.PreBookedDateNoTime, JOB_CONTROL_VIEW.JobDateIn, JOB_CONTROL_VIEW.Estimate_Appointment_DateTime, JOB_CONTROL_VIEW.HandOverDateTime, JOB_CONTROL_VIEW.ReqCompDate, JOB_CONTROL_VIEW.Delivery_DateTime, JOB_CONTROL_VIEW.TotalInvoiceableNet, JOB_CONTROL_VIEW.TotalInvoicedNet, JOB_CONTROL_VIEW.TotalNotInvoicedNet, JOB_CONTROL_VIEW.DaysCurrentStatus, JOB_CONTROL_VIEW.DaysOnSite, JOB_CONTROL_VIEW.DaysInProgress, JOB_CONTROL_VIEW.ClaimHandler, JOB_CONTROL_VIEW.WorkProviderName, JOB_CONTROL_VIEW.CancelledDate, JOB_CONTROL_VIEW.JobExcessDisplay, JOB_CONTROL_VIEW.EstMETHours, JOB_CONTROL_VIEW.EstPanelHours, JOB_CONTROL_VIEW.EstPaintHours, JOB_CONTROL_VIEW.EstAllowHours, JOB_CONTROL_VIEW.repex_code
FROM DBA.JOB_CONTROL_VIEW JOB_CONTROL_VIEW

[HansV]

Dave,

I have moved this thread to the Access/SQL forum, because your question is about SQL, even if you use it from Excel.

MS Query uses SQL, just like Access. You can't use functions that are specific to Access, such as Nz, but you can use many VBA functions. To get rid of the time component of JobCreationDate, you can use the DateValue function:

..., DateValue(JOB_CONTROL_VIEW.JobCreationDate) AS JobCreationDate, ...

[D Willett]

Hi Hans
MS Query is new to me and at the moment a new language to learn ( How many times have I been here lol).
Trying the expression I get Syntax error. Not sure where I apply this in the builder ....

[HansV]

You should use the expression in the SQL of the query, not in the Criteria.

SELECT JOB_CONTROL_VIEW.JobID, JOB_CONTROL_VIEW.VehicleReg, JOB_CONTROL_VIEW.VehicleMake, JOB_CONTROL_VIEW.VehicleModel, JOB_CONTROL_VIEW.EstimatorCode, DateValue(JOB_CONTROL_VIEW.JobCreationDate) AS JobCreationDate, JOB_CONTROL_VIEW.JobAmendDate, JOB_CONTROL_VIEW.DiscountParts, JOB_CONTROL_VIEW.DiscountLabour, JOB_CONTROL_VIEW.DiscountMaterials, JOB_CONTROL_VIEW.DiscountSpecialist, JOB_CONTROL_VIEW.DiscountOverall, JOB_CONTROL_VIEW.EstLabourHours, JOB_CONTROL_VIEW.EstLabourNet, JOB_CONTROL_VIEW.EstMaterialNet, JOB_CONTROL_VIEW.EstPartsNet, JOB_CONTROL_VIEW.EstSpecialistNet, JOB_CONTROL_VIEW.EstTotalNet, JOB_CONTROL_VIEW.EstBottomLineDiscAmount, JOB_CONTROL_VIEW.JobStatus, JOB_CONTROL_VIEW.Notification_DateTime, JOB_CONTROL_VIEW.Vehicle_WriteOff, JOB_CONTROL_VIEW.Work_Provider, JOB_CONTROL_VIEW.JobDateOutExpected, JOB_CONTROL_VIEW.JobCurrentLocation, JOB_CONTROL_VIEW.CompletionDate, JOB_CONTROL_VIEW.Insured, JOB_CONTROL_VIEW.CPExplicitStartDate, JOB_CONTROL_VIEW.OrigExpCompDate, JOB_CONTROL_VIEW.ImportedLabourHours, JOB_CONTROL_VIEW.HoursRemaining, JOB_CONTROL_VIEW.EstimatedDate, JOB_CONTROL_VIEW.AuthorisedDate, JOB_CONTROL_VIEW.InProgressDate, JOB_CONTROL_VIEW.InvoicedDate, JOB_CONTROL_VIEW.WriteOffDate, JOB_CONTROL_VIEW.Insurer, JOB_CONTROL_VIEW.JobType, JOB_CONTROL_VIEW.LabourRate, JOB_CONTROL_VIEW.AllocHours, JOB_CONTROL_VIEW.ProdHoursBooked, JOB_CONTROL_VIEW.ActualHoursBooked, JOB_CONTROL_VIEW.Collection_DateTime, JOB_CONTROL_VIEW.Recovery_DateTime, JOB_CONTROL_VIEW.PreBookedDateNoTime, JOB_CONTROL_VIEW.JobDateIn, JOB_CONTROL_VIEW.Estimate_Appointment_DateTime, JOB_CONTROL_VIEW.HandOverDateTime, JOB_CONTROL_VIEW.ReqCompDate, JOB_CONTROL_VIEW.Delivery_DateTime, JOB_CONTROL_VIEW.TotalInvoiceableNet, JOB_CONTROL_VIEW.TotalInvoicedNet, JOB_CONTROL_VIEW.TotalNotInvoicedNet, JOB_CONTROL_VIEW.DaysCurrentStatus, JOB_CONTROL_VIEW.DaysOnSite, JOB_CONTROL_VIEW.DaysInProgress, JOB_CONTROL_VIEW.ClaimHandler, JOB_CONTROL_VIEW.WorkProviderName, JOB_CONTROL_VIEW.CancelledDate, JOB_CONTROL_VIEW.JobExcessDisplay, JOB_CONTROL_VIEW.EstMETHours, JOB_CONTROL_VIEW.EstPanelHours, JOB_CONTROL_VIEW.EstPaintHours, JOB_CONTROL_VIEW.EstAllowHours, JOB_CONTROL_VIEW.repex_code
FROM DBA.JOB_CONTROL_VIEW JOB_CONTROL_VIEW

[D Willett]

Procedure Error: DateValue not found

Not sure if this is a text or date format problem now. I'll have to dig around Google some more lol...

[HansV]

Does the following work?

DateSerial(Year(JOB_CONTROL_VIEW.JobCreationDate), Month(JOB_CONTROL_VIEW.JobCreationDate), Day(JOB_CONTROL_VIEW.JobCreationDate)) AS JobCreationDate

Another option is to format the resulting column in Excel as a date dd/mm/yyyy or dd-mmm-yyyy.

[D Willett]

Something tells me this is a fixed width field/column:

2011-11-16 00:00:00.000000

Which would make this a text and therefore formatting would shift part data along and merge into existing fields..

[HansV]

How about

Left(JOB_CONTROL_VIEW.JobCreationDate,10) AS JobCreationDate

[D Willett]

For the moment: Between #01/01/2012# And #31/01/2012# works to get the current months data. This would mean editing the criteria each month.
I guess the time element isn't doing anything as it's all zero's. What would the criteria be to collect the current months data, trying Month() as criteria gives syntax error.

[HansV]

Does this work?

Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Mont(Date())+1, 0)

[D Willett]

Still didn't work Hans.
I think with the problems I've had with the date column I should find another way. Obviously this data doesn't want to play ball.
Could I for instance use two cells as StartDate and EndDate and then pass those parameters into ms query?
I have the cells populated with First of current month and end of current month.

I really don't want to waste you time if I can help it and you've really been helpful.

[HansV]

See if Customize a parameter query helps, in particular the section "Use data from a cell as a parameter value".

[D Willett]

Still not happening and giving this a rest for awhile.
The parameter icon is ghosted out and can't get it to go live. Ive used various types of criteria format ie [StartDate]........... #01/01/2012#........ selected cells from the source data and also the destination data. Just can't get anything to work with the data.

[HansV]

I'm afraid I'm stumped...

[D Willett]

Me too........ so frustrating

[D Willett]

Ok I'm back with a different approach. I have to use Access and accept it which should make the parameters easier to deal with.
The following VBA looks through the recordset, recognises my Cell data: A2 & A3 as StartDate & EndDate but only creates the headers with no data underneath.
So I get a row of headers and nothing else:

Code: Select all

Sub RunParameterQuery()
 
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("L:\AF-Dash\AF-Dash.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qryDBA_JOB_CONTROL_VIEW")
 
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[StartDate]") = Range("A2").Value
.Parameters("[EndDate]") = Range("A3").Value
End With
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
Sheets("Sheet1").Select
ActiveSheet.Range("A5:K10000").ClearContents
 
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A6").CopyFromRecordset MyRecordset
 
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
 
MsgBox "Your Query has been Run"
 
End Sub

[HansV]

The line

ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name

should be

ActiveSheet.Cells(5, i).Value = MyRecordset.Fields(i - 1).Name

The original version overwrites the first record of the recordset. Do you get a record in row 6 now, or is it blank?

[D Willett]

No I get a record in 5 now but it's just the header names. I get no data under the headers.
If I run the query through Access I get 30-40 records with my date range of [StartDate]=01/12/2012 to [EndDate]=10/01/2012.

[HansV]

Have you declared [StartDate] and [EndDate] explicitly as parameters of type Date in Access, in the Parameters dialog?

[D Willett]

Yes, properties-Short Date and formatted the cell A2:A3 of excel too.