Format time in query

[Costefran]

Can anyoner help I currently have a union query which produces a time format as 00:00:00 in its results despite the fact that the two queries that form the union query produce a time format in 00:00

I'm not sure why this happens but would like to change the format back and not sure how so any help would be appreciated

Also, I have a time field (hours and minutes) on my form and would like to prevent users from entering times between full hours

so

09:00 is okay
09:15 is not okay
09:30 is not okay
09:45 is not okay
10:00 is okay

Any help would be appreciated

 

[SkipVought]

Please post your SQL code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Costefran]

Here is the SQL for my first query

SELECT [14 Day Outlook Fields Table].Reference, [14 Day Outlook Fields Table].[CAP Training], [14 Day Outlook Fields Table].[Template Created Count], [14 Day Outlook Fields Table].[Start Day], [14 Day Outlook Fields Table].[End Day], [14 Day Outlook Fields Table].Region, [14 Day Outlook Fields Table].[Date of Entry], [14 Day Outlook Fields Table].[CEWA Level], [14 Day Outlook Fields Table].[CEWA Reference], [14 Day Outlook Fields Table].[Days to CEWA from Current Day], [14 Day Outlook Fields Table].[Start Date], [14 Day Outlook Fields Table].[End Date], [14 Day Outlook Fields Table].[Days Nights Sequential], [14 Day Outlook Fields Table].[Start Time for each work period], [14 Day Outlook Fields Table].[End Time for each work period], [14 Day Outlook Fields Table].[System to Be Worked on], [14 Day Outlook Fields Table].[Description of Work], [14 Day Outlook Fields Table].[Maintenance or Construction], [14 Day Outlook Fields Table].[Owner of Works], [14 Day Outlook Fields Table].[Contact Number], [14 Day Outlook Fields Table].Building, [14 Day Outlook Fields Table].Address, [14 Day Outlook Fields Table].[Planned Clients / Businesses impacted], [14 Day Outlook Fields Table].[Potential Clients / Businesses impacted], [14 Day Outlook Fields Table].[Work Risk / Mitigation Description], [14 Day Outlook Fields Table].[Local Approval], [14 Day Outlook Fields Table].[USA Approval], [14 Day Outlook Fields Table].Other, 0 AS [MaxofClash or Not], "RCP or Other Event" AS [Clash or Not], [14 Day Outlook Fields Table].[All Disablements]
FROM [14 Day Outlook Fields Table]
WHERE ((([14 Day Outlook Fields Table].Region) Like [Forms]![Log In Form]![Search by Region3]) AND (([14 Day Outlook Fields Table].[CEWA Level]) Like [Forms]![Log In Form]![Search by CEWA Level3]) AND (([14 Day Outlook Fields Table].[CEWA Reference]) Like [Forms]![Log In Form]![Search by CEWA Reference3]) AND (([14 Day Outlook Fields Table].[Start Date])=[Forms]![Log In Form]![Search by Start Date2]) AND (([14 Day Outlook Fields Table].[System to Be Worked on]) Like [Forms]![Log In Form]![Search by system to be worked on3]) AND (([14 Day Outlook Fields Table].[Owner of Works]) Like [Forms]![Log In Form]![Search by Owner of Works3]) AND (([14 Day Outlook Fields Table].Building) Like [Forms]![Log In Form]![Search by Building3]) AND (([14 Day Outlook Fields Table].[Local Approval]) Like [Forms]![Log In Form]![Search by Approvals Local3]) AND (([14 Day Outlook Fields Table].[USA Approval]) Like [Forms]![Log In Form]![Search by Approvals USA3]));

Here is the SQL for my 2nd Query
SELECT [14 Day Outlook Fields Table].Reference, [14 Day Outlook Fields Table].[CAP Training], [14 Day Outlook Fields Table].[Template Created Count], [14 Day Outlook Fields Table].[Start Day], [14 Day Outlook Fields Table].[End Day], [14 Day Outlook Fields Table].Region, [14 Day Outlook Fields Table].[Date of Entry], [14 Day Outlook Fields Table].[CEWA Level], [14 Day Outlook Fields Table].[CEWA Reference], [14 Day Outlook Fields Table].[Days to CEWA from Current Day], [14 Day Outlook Fields Table].[Start Date], [14 Day Outlook Fields Table].[End Date], [14 Day Outlook Fields Table].[Days Nights Sequential], [14 Day Outlook Fields Table].[Start Time for each work period], [14 Day Outlook Fields Table].[End Time for each work period], [14 Day Outlook Fields Table].[System to Be Worked on], [14 Day Outlook Fields Table].[Description of Work], [14 Day Outlook Fields Table].[Maintenance or Construction], [14 Day Outlook Fields Table].[Owner of Works], [14 Day Outlook Fields Table].[Contact Number], [14 Day Outlook Fields Table].Building, [14 Day Outlook Fields Table].Address, [14 Day Outlook Fields Table].[Planned Clients / Businesses impacted], [14 Day Outlook Fields Table].[Potential Clients / Businesses impacted], [14 Day Outlook Fields Table].[Work Risk / Mitigation Description], [14 Day Outlook Fields Table].[Local Approval], [14 Day Outlook Fields Table].[USA Approval], [14 Day Outlook Fields Table].Other, 0 AS [MaxofClash or Not], "RCP or Other Event" AS [Clash or Not], [14 Day Outlook Fields Table].[All Disablements]
FROM [14 Day Outlook Fields Table]
WHERE ((([14 Day Outlook Fields Table].Region) Like [Forms]![Log In Form]![Search by Region3]) AND (([14 Day Outlook Fields Table].[CEWA Level]) Like [Forms]![Log In Form]![Search by CEWA Level3]) AND (([14 Day Outlook Fields Table].[CEWA Reference]) Like [Forms]![Log In Form]![Search by CEWA Reference3]) AND (([14 Day Outlook Fields Table].[Start Date])<[Forms]![Log In Form]![Search by Start Date2]) AND (([14 Day Outlook Fields Table].[End Date])>[Forms]![Log In Form]![Search by Start Date2]) AND (([14 Day Outlook Fields Table].[System to Be Worked on]) Like [Forms]![Log In Form]![Search by system to be worked on3]) AND (([14 Day Outlook Fields Table].[Owner of Works]) Like [Forms]![Log In Form]![Search by Owner of Works3]) AND (([14 Day Outlook Fields Table].Building) Like [Forms]![Log In Form]![Search by Building3]) AND (([14 Day Outlook Fields Table].[Local Approval]) Like [Forms]![Log In Form]![Search by Approvals Local3]) AND (([14 Day Outlook Fields Table].[USA Approval]) Like [Forms]![Log In Form]![Search by Approvals USA3]));


And here is the UNION query
SELECT [Record_Report Query with Start Date Pre1].*
FROM [Record_Report Query with Start Date Pre1];
UNION ALL SELECT [Record_Report Query with Start Date Pre2].*
FROM [Record_Report Query with Start Date Pre2];

 

[SkipVought]

You never stated WHICH FIELD is the one???

You might consider helping yourself out by 1) structuring your code to be more undertandable and 2) providing some guidance of where to look in this.

Time Data is just a number. The "format" is just what you see. Is someone going to manipulate the data from the query of just LOOK at it? If the Latter, then use the FORMAT function to convert to a STRING in the format of choice.

SELECT
  Reference
, [CAP Training]
, [Template Created Count]
, [Start Day]
, [End Day]
, Region
, [Date of Entry]
, [CEWA Level]
, [CEWA Reference]
, [Days to CEWA from Current Day]
, [Start Date]
, [End Date]
, [Days Nights Sequential]
, [Start Time for each work period]
, [End Time for each work period]
, [System to Be Worked on]
, [Description of Work]
, [Maintenance or Construction]
, [Owner of Works]
, [Contact Number]
, Building
, Address
, [Planned Clients / Businesses impacted]
, [Potential Clients / Businesses impacted]
, [Work Risk / Mitigation Description]
, [Local Approval]
, [USA Approval]
, Other
, 0 AS [MaxofClash or Not]
, "RCP or Other Event" AS [Clash or Not]
, [All Disablements]

FROM [14 Day Outlook Fields Table]

WHERE (((Region) Like [Forms]![Log In Form]![Search by Region3])
  AND (([CEWA Level]) Like [Forms]![Log In Form]![Search by CEWA Level3])
  AND (([CEWA Reference]) Like [Forms]![Log In Form]![Search by CEWA Reference3])
  AND (([Start Date])=[Forms]![Log In Form]![Search by Start Date2])
  AND (([System to Be Worked on]) Like [Forms]![Log In Form]![Search by system to be worked on3])
  AND (([Owner of Works]) Like [Forms]![Log In Form]![Search by Owner of Works3])
  AND ((Building) Like [Forms]![Log In Form]![Search by Building3])
  AND (([Local Approval]) Like [Forms]![Log In Form]![Search by Approvals Local3])
  AND (([USA Approval]) Like [Forms]![Log In Form]![Search by Approvals USA3]));

Also, this statement COULD be a problem, but if you're getting the correct data returned, I guess not.

AND (([Start Date])=[Forms]![Log In Form]![Search by Start Date2])

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Golom]

As Skip says, you haven't identified which field you need formatted. The usual mechanism is

Format([Some DateTIme Field], "hh:nn")

The 00:00:00 formating is probably coming by default from your local settings.

As to the field on your form, can you just modify it to display hours only without the minutes portion or are you also using it for something else where minutes are relevant?

 

[Costefran]

Thanks for this to explain further, the union query is then appended to a table which is uploaded to an exel temple. The excel template then carries out some conditional formatting based on the uploaded cell against a fixed value

So if(uploaded cell > fixed value,1,0)

Because the uploaded cell format is now 00:00:00 and the fixed value is 00:00 I cannot get the conditional formatting to work. If I delete the last two zeros from the uploaded cell format it works fine

I haver tried reformatting in excel but no luck so thought it was best to do this in access

Thanks

 

[SkipVought]

TIME is just a NUMBER!!!

FORMAT is just what you SEE!!!

Export the TIME -- AS IS.

Format the data in Excel OR use the TEXT(YourTimeCell,"hh:mm") in your CF to contol the format you want to test.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]