Adding Calculations To SQL Query Builder

G

Guest

I am adding my last calculation to query builder for a report. I run the
query and I get the correct result.

However when I say the changes to SQL I get the following message: Microsoft
Access – The string returned by the builder was too long. The result will be
truncated.

I select ok. Save my changes. Run the report and I get Syntax error in
ORDER BY clause. (Error 3138)

I have long field names in my table and the table name is long.

Is the best way to solve this problem create a table with a shorter field
name and shorter table name ???

Any help is appreciated
 
C

Chris2

dskanes said:
I am adding my last calculation to query builder for a report. I run the
query and I get the correct result.

However when I say the changes to SQL I get the following message: Microsoft
Access – The string returned by the builder was too long. The result will be
truncated.

I select ok. Save my changes. Run the report and I get Syntax error in
ORDER BY clause. (Error 3138)

I have long field names in my table and the table name is long.

Is the best way to solve this problem create a table with a shorter field
name and shorter table name ???

Any help is appreciated
Click to expand...


dskanes,

1) Open your Query in Design View.
2) Spot each Table in the top pane.
3) Right-click on the title/name of the Table.
4) Select "Properties".
5) In the Properties Dialog Box, Add a value to Alias (see below).
6) Repeat for all Tables.


You should select a 2 to 4 (2, most often) character "Alias" for your
tables.
Examples (as seen in SQL View):
CustomerMaster AS C1
CustomerMaster AS CM1
Customers AS C1

SalesDetails AS SD1

For repeat occurrences of the same Table in the Query, increment the
numeric trailer (2, 3, 4, etc.)

For subqueries, use 01, 02, 03, 04, etc., to indicate the depth of the
subquery.

By using Table aliases, you will shortern your Queries and make your
SQL *vastly* more readable.

Sincerely,

Chris O.
 
G

Guest

Hi Chris,

Thanks I changed the alias on my tables which solved my original problem and
Yes it makes SQL more readable.

However I noticed a couple of things that are now happening that I can’t seem
To resolve and I am a bit confused:

1. I get prompted to enter parameter value for the following fields:
Actualdailyhours, dailyhours, statutory. These fields are not calculated
values
In my query they are fields values from a table and the show is selected in
the query.
Also the query parameter box is empty.

2. My calculations no longer work for Excephrs, Reghrs, or Othrs. When I run
the SQL they all show up as 0.

3. I noticed some detail records are being skipped and not reported on my
report. For a given date there can be multiple records for same driver for
the same day with same route and routes cat.

I will insert my SQL not sure if its needed :

SELECT DSYD.DriverID, [FirstName] & " " & [LastName] AS Name,
DSYD.SchWorkDate, DSYD.[Route#], DSYD.RouteCategory, DSYD.NumOfBreaks AS
TotalBreak, DSYD.DailyHours, DSYD.ActualDailyHours, DSYD.Statutory,
IIf([DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHours])
AS ExceptHrs,
Switch([DriverSchedule_YTD]![Statutory]="Y",0,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![ActualDailyHours],[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours],True,0)
AS Reghrs,
Switch([DriverSchedule_YTD]![Statutory]="N",0,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![ActualDailyHours],[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours],True,0) AS Othrs
FROM DriversDescription AS DD1 INNER JOIN DriverSchedule_YTD AS DSYD ON
DD1.DriverID = DSYD.DriverID
GROUP BY DSYD.DriverID, [FirstName] & " " & [LastName], DSYD.SchWorkDate,
DSYD.[Route#], DSYD.RouteCategory, DSYD.NumOfBreaks, DSYD.DailyHours,
DSYD.ActualDailyHours, DSYD.Statutory,
IIf([DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHours]),
Switch([DriverSchedule_YTD]![Statutory]="Y",0,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![ActualDailyHours],[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours],True,0),
Switch([DriverSchedule_YTD]![Statutory]="N",0,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![ActualDailyHours],[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours],True,0)
HAVING (((DSYD.SchWorkDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (DSYD.SchWorkDate)<=[forms]![Report Date Range]![Ending Order
Date]))
ORDER BY DSYD.DriverID, DSYD.SchWorkDate, DSYD.[Route#], DSYD.RouteCategory;


Any help is appreciated as I have been days trying to resolve my issues with
this
report.

Thanks
 
C

Chris2

dskanes said:
Hi Chris,

Thanks I changed the alias on my tables which solved my original problem and
Yes it makes SQL more readable.

However I noticed a couple of things that are now happening that I can’t seem
To resolve and I am a bit confused:

1. I get prompted to enter parameter value for the following fields:
Actualdailyhours, dailyhours, statutory. These fields are not calculated
values
In my query they are fields values from a table and the show is selected in
the query.
Also the query parameter box is empty.

2. My calculations no longer work for Excephrs, Reghrs, or Othrs. When I run
the SQL they all show up as 0.

3. I noticed some detail records are being skipped and not reported on my
report. For a given date there can be multiple records for same driver for
the same day with same route and routes cat.

I will insert my SQL not sure if its needed :
Click to expand...

Any help is appreciated as I have been days trying to resolve my issues with
this
report.

Thanks
Click to expand...


dskanes,

The Query, realigned for readability:

SELECT DSYD.DriverID
,[FirstName] & " " & [LastName] AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf([DriverSchedule_YTD]![ActualDailyHours]>0

,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHo
urs]) AS ExceptHrs
,Switch([DriverSchedule_YTD]![Statutory]="Y",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Reghrs
,Switch([DriverSchedule_YTD]![Statutory]="N",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
GROUP BY DSYD.DriverID
,[FirstName] & " " & [LastName]
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf([DriverSchedule_YTD]![ActualDailyHours]>0

,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHo
urs])
,Switch([DriverSchedule_YTD]![Statutory]="Y",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0)
,Switch([DriverSchedule_YTD]![Statutory]="N",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0)
HAVING (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
And (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;



First, I cannot figure out why you are doing a GROUP BY. There is no
Aggregation (SUM, COUNT, MAX, MIN). While access will let you do the
following (run on one of my example tables), which is the equivalent
of what is being done above:

SELECT I1.ItemID
,I1.Description
,I1.VendorItemID
FROM ItemMaster AS I1
GROUP BY I1.ItemID
,I1.Description
,I1.VendorItemID

It's the same as:

SELECT *
FROM ItemMaster AS I1

So . . . there is no purpose to the GROUP BY above. I delete it,
along with the HAVING, turning that part into a WHERE clause.


SELECT DSYD.DriverID
,[FirstName] & " " & [LastName] AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf([DriverSchedule_YTD]![ActualDailyHours]>0

,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHo
urs]) AS ExceptHrs
,Switch([DriverSchedule_YTD]![Statutory]="Y",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Reghrs
,Switch([DriverSchedule_YTD]![Statutory]="N",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;


But wait, there are still a few adjustments to make, as Access didn't
quite change all the table names into the their aliases for you.


SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS ExceptHrs
,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;

There, that cleans up the last of the Table aliases.

As to why all the calculations turn up 0, that depends on what data
you're running through the Query.

Select a small set of rows, and run your query on them to make sure
the Query still produces zeroes for those columns. Then present the
sample data here, including the dates currently being entered on the
form.


Sincerely,

Chris O.
 
G

Guest

Hi Chris,

Thanks again for your response. I made all the changes you suggested and
Now the calculations work ok.

I still have the weird problem of skipping some records in the SQL.

This is the data in the YTD file:

DriverID SchWorkDate Route. RouteCategory NumOfBreaks DailyHours
7091 2/3/2005 003-001-S PM 0 6.51
7965 2/3/2005 099-035-N PM 0 6.08
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17

I enter the following dates on the form : Starting Date : 01/02/04 and
Ending date 02/03/05

The result is only 2 record show up on the report and in SQL the same 2
records

7965 2/3/2005 099-035-N PM 0 6.08

7091 2/3/2005 003-001-S PM 0 6.51

This function worked @ one point but I am not sure what is happening here.
Any help is much appreciated. Does the input file have to be sorted before
it hits the form with the start and end date ?









Chris2 said:
dskanes said:
Hi Chris,

Thanks I changed the alias on my tables which solved my original problem and
Yes it makes SQL more readable.

However I noticed a couple of things that are now happening that I can’t seem
To resolve and I am a bit confused:

1. I get prompted to enter parameter value for the following fields:
Actualdailyhours, dailyhours, statutory. These fields are not calculated
values
In my query they are fields values from a table and the show is selected in
the query.
Also the query parameter box is empty.

2. My calculations no longer work for Excephrs, Reghrs, or Othrs. When I run
the SQL they all show up as 0.

3. I noticed some detail records are being skipped and not reported on my
report. For a given date there can be multiple records for same driver for
the same day with same route and routes cat.

I will insert my SQL not sure if its needed :
Click to expand...

Any help is appreciated as I have been days trying to resolve my issues with
this
report.

Thanks
Click to expand...


dskanes,

The Query, realigned for readability:

SELECT DSYD.DriverID
,[FirstName] & " " & [LastName] AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf([DriverSchedule_YTD]![ActualDailyHours]>0

,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHo
urs]) AS ExceptHrs
,Switch([DriverSchedule_YTD]![Statutory]="Y",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Reghrs
,Switch([DriverSchedule_YTD]![Statutory]="N",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
GROUP BY DSYD.DriverID
,[FirstName] & " " & [LastName]
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf([DriverSchedule_YTD]![ActualDailyHours]>0

,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHo
urs])
,Switch([DriverSchedule_YTD]![Statutory]="Y",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0)
,Switch([DriverSchedule_YTD]![Statutory]="N",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0)
HAVING (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
And (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;



First, I cannot figure out why you are doing a GROUP BY. There is no
Aggregation (SUM, COUNT, MAX, MIN). While access will let you do the
following (run on one of my example tables), which is the equivalent
of what is being done above:

SELECT I1.ItemID
,I1.Description
,I1.VendorItemID
FROM ItemMaster AS I1
GROUP BY I1.ItemID
,I1.Description
,I1.VendorItemID

It's the same as:

SELECT *
FROM ItemMaster AS I1

So . . . there is no purpose to the GROUP BY above. I delete it,
along with the HAVING, turning that part into a WHERE clause.


SELECT DSYD.DriverID
,[FirstName] & " " & [LastName] AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf([DriverSchedule_YTD]![ActualDailyHours]>0

,[DriverSchedule_YTD]![ActualDailyHours]-[DriverSchedule_YTD]![DailyHo
urs]) AS ExceptHrs
,Switch([DriverSchedule_YTD]![Statutory]="Y",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Reghrs
,Switch([DriverSchedule_YTD]![Statutory]="N",0

,[DriverSchedule_YTD]![ActualDailyHours]>0,[DriverSchedule_YTD]![Actua
lDailyHours]

,[DriverSchedule_YTD]![DailyHours]>0,[DriverSchedule_YTD]![DailyHours]
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;


But wait, there are still a few adjustments to make, as Access didn't
quite change all the table names into the their aliases for you.


SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS ExceptHrs
,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;

There, that cleans up the last of the Table aliases.

As to why all the calculations turn up 0, that depends on what data
you're running through the Query.

Select a small set of rows, and run your query on them to make sure
the Query still produces zeroes for those columns. Then present the
sample data here, including the dates currently being entered on the
form.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

dskanes said:
SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS ExceptHrs
,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;
Click to expand...
Hi Chris,

Thanks again for your response. I made all the changes you suggested and
Now the calculations work ok.

I still have the weird problem of skipping some records in the SQL.

This is the data in the YTD file:

DriverID SchWorkDate Route. RouteCategory NumOfBreaks DailyHours
7091 2/3/2005 003-001-S PM 0 6.51
7965 2/3/2005 099-035-N PM 0 6.08
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17

I enter the following dates on the form : Starting Date : 01/02/04 and
Ending date 02/03/05

The result is only 2 record show up on the report and in SQL the same 2
records

7965 2/3/2005 099-035-N PM 0 6.08

7091 2/3/2005 003-001-S PM 0 6.51

This function worked @ one point but I am not sure what is happening here.
Any help is much appreciated. Does the input file have to be sorted before
it hits the form with the start and end date ?
Click to expand...

dskanes,

I *think* I see something that may be causing problems:

WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date Range]![Beginning
Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))

Switch the above to:

WHERE (((DSYD.SchWorkDate) >= CDate([forms]![Report Date
Range]![Beginning Order Date])
AND (DSYD.SchWorkDate) <= CDate([forms]![Report Date
Range]![Ending Order Date])))


Let me know if that works.


Sincerely,

Chris O.
 
G

Guest

Chris2 said:
dskanes said:
SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS ExceptHrs
,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;
Click to expand...
Hi Chris,

Thanks again for your response. I made all the changes you suggested and
Now the calculations work ok.

I still have the weird problem of skipping some records in the SQL.

This is the data in the YTD file:

DriverID SchWorkDate Route. RouteCategory NumOfBreaks DailyHours
7091 2/3/2005 003-001-S PM 0 6.51
7965 2/3/2005 099-035-N PM 0 6.08
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17

I enter the following dates on the form : Starting Date : 01/02/04 and
Ending date 02/03/05

The result is only 2 record show up on the report and in SQL the same 2
records

7965 2/3/2005 099-035-N PM 0 6.08

7091 2/3/2005 003-001-S PM 0 6.51

This function worked @ one point but I am not sure what is happening here.
Any help is much appreciated. Does the input file have to be sorted before
it hits the form with the start and end date ?
Click to expand...

dskanes,

I *think* I see something that may be causing problems:

WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date Range]![Beginning
Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))

Switch the above to:

WHERE (((DSYD.SchWorkDate) >= CDate([forms]![Report Date
Range]![Beginning Order Date])
AND (DSYD.SchWorkDate) <= CDate([forms]![Report Date
Range]![Ending Order Date])))


Let me know if that works.


Sincerely,

Chris O.

Hi Chris
Click to expand...

Thanks for all your help.

Tried the latest sql changes but no luck. Then ran the SQL and sql picks up
the 2 records only.

If I run the report the form comes up and type in the beginning and ending
date.
Then I get another 2 prompt : enter parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Then on the report I get the same 2 records -7965 and 7091.

Not Sure why the dates have to be entered twice ?

Thanks for your time and please let me know if you need further details
Doug
 
G

Guest

Chris2 said:
dskanes said:
SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS ExceptHrs
,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;
Click to expand...
Hi Chris,

Thanks again for your response. I made all the changes you suggested and
Now the calculations work ok.

I still have the weird problem of skipping some records in the SQL.

This is the data in the YTD file:

DriverID SchWorkDate Route. RouteCategory NumOfBreaks DailyHours
7091 2/3/2005 003-001-S PM 0 6.51
7965 2/3/2005 099-035-N PM 0 6.08
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17

I enter the following dates on the form : Starting Date : 01/02/04 and
Ending date 02/03/05

The result is only 2 record show up on the report and in SQL the same 2
records

7965 2/3/2005 099-035-N PM 0 6.08

7091 2/3/2005 003-001-S PM 0 6.51

This function worked @ one point but I am not sure what is happening here.
Any help is much appreciated. Does the input file have to be sorted before
it hits the form with the start and end date ?
Click to expand...

dskanes,

I *think* I see something that may be causing problems:

WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date Range]![Beginning
Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))

Switch the above to:

WHERE (((DSYD.SchWorkDate) >= CDate([forms]![Report Date
Range]![Beginning Order Date])
AND (DSYD.SchWorkDate) <= CDate([forms]![Report Date
Range]![Ending Order Date])))


Let me know if that works.


Sincerely,

Chris O.

Hi Chris
Click to expand...

Thanks for all your help.

Tried the latest sql changes but no luck. Then ran the SQL and sql picks up
the 2 records only.

If I run the report the form comes up and type in the beginning and ending
date.
Then I get another 2 prompt : enter parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Then on the report I get the same 2 records -7965 and 7091.

Not Sure why the dates have to be entered twice ?

Thanks for your time and please let me know if you need further details
 
G

Guest

Hi Chris

Thanks for all your help. Sorry I posted twice yesterday . I just wanted to
give you an update the latest changes get the records in SQL and on the
report now.

The only problem I have is if I run the report the form comes up and I type
in the beginning and ending date. Then I get another 2 prompt : enter
parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Any help is appreciated

Chris2 said:
dskanes said:
SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS ExceptHrs
,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;

Hi Chris,

Thanks again for your response. I made all the changes you suggested and
Now the calculations work ok.

I still have the weird problem of skipping some records in the SQL.

This is the data in the YTD file:

DriverID SchWorkDate Route. RouteCategory NumOfBreaks DailyHours
7091 2/3/2005 003-001-S PM 0 6.51
7965 2/3/2005 099-035-N PM 0 6.08
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17

I enter the following dates on the form : Starting Date : 01/02/04 and
Ending date 02/03/05

The result is only 2 record show up on the report and in SQL the same 2
records

7965 2/3/2005 099-035-N PM 0 6.08

7091 2/3/2005 003-001-S PM 0 6.51

This function worked @ one point but I am not sure what is happening here.
Any help is much appreciated. Does the input file have to be sorted before
it hits the form with the start and end date ?
Click to expand...

dskanes,

I *think* I see something that may be causing problems:

WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date Range]![Beginning
Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))

Switch the above to:

WHERE (((DSYD.SchWorkDate) >= CDate([forms]![Report Date
Range]![Beginning Order Date])
AND (DSYD.SchWorkDate) <= CDate([forms]![Report Date
Range]![Ending Order Date])))


Let me know if that works.


Sincerely,

Chris O.

Hi Chris
Click to expand...

Thanks for all your help.

Tried the latest sql changes but no luck. Then ran the SQL and sql picks up
the 2 records only.

If I run the report the form comes up and type in the beginning and ending
date.
Then I get another 2 prompt : enter parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Then on the report I get the same 2 records -7965 and 7091.

Not Sure why the dates have to be entered twice ?

Thanks for your time and please let me know if you need further details
Click to expand...
 
S

SteveS

PMFJI

I noticed three things:

1) In all of the other posts, you typed

[forms]![Report Date Range]![Beginning Order Date]

AND

[forms]![Report Date Range]![Ending Order Date]


spaces in the form name, but now you show no spaces in the form name:


Form!ReportDateRange!Beginning Date

and

Form!ReportDateRange!Ending Order Date.


2) It should be FORMS!Report.. (FORMS with an 'S')

3) Is it [Beginning Order Date] or [Beginning Date]?

I never use spaces in object names (no one see them and spaces makes it
a lot harder when you have to use VB or SQL). Because of the spaces, you
might need to use quotes or brackets.

Maybe not the problems, but something to check...

--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hi Chris

Thanks for all your help. Sorry I posted twice yesterday . I just wanted to
give you an update the latest changes get the records in SQL and on the
report now.

The only problem I have is if I run the report the form comes up and I type
in the beginning and ending date. Then I get another 2 prompt : enter
parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Any help is appreciated


:

SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS

ExceptHrs

,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date

Range]![Ending

Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;


Hi Chris,

Thanks again for your response. I made all the changes you suggested

and

Now the calculations work ok.

I still have the weird problem of skipping some records in the SQL.

This is the data in the YTD file:

DriverID SchWorkDate Route. RouteCategory NumOfBreaks

DailyHours

7091 2/3/2005 003-001-S PM 0 6.51
7965 2/3/2005 099-035-N PM 0 6.08
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17

I enter the following dates on the form : Starting Date : 01/02/04

and

Ending date 02/03/05

The result is only 2 record show up on the report and in SQL the

same 2

records

7965 2/3/2005 099-035-N PM 0 6.08

7091 2/3/2005 003-001-S PM 0 6.51

This function worked @ one point but I am not sure what is happening

here.

Any help is much appreciated. Does the input file have to be sorted

before

it hits the form with the start and end date ?

dskanes,

I *think* I see something that may be causing problems:

WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date Range]![Beginning
Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))

Switch the above to:

WHERE (((DSYD.SchWorkDate) >= CDate([forms]![Report Date
Range]![Beginning Order Date])
AND (DSYD.SchWorkDate) <= CDate([forms]![Report Date
Range]![Ending Order Date])))


Let me know if that works.


Sincerely,

Chris O.

Hi Chris
Click to expand...

Thanks for all your help.

Tried the latest sql changes but no luck. Then ran the SQL and sql picks up
the 2 records only.

If I run the report the form comes up and type in the beginning and ending
date.
Then I get another 2 prompt : enter parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Then on the report I get the same 2 records -7965 and 7091.

Not Sure why the dates have to be entered twice ?

Thanks for your time and please let me know if you need further details
Click to expand...
Click to expand...
 
G

Guest

Just wanted to follow up and thank you guys for your help. Actually the
spaces between the field names were causing the problem

SteveS said:
PMFJI

I noticed three things:

1) In all of the other posts, you typed

[forms]![Report Date Range]![Beginning Order Date]

AND

[forms]![Report Date Range]![Ending Order Date]


spaces in the form name, but now you show no spaces in the form name:


Form!ReportDateRange!Beginning Date

and

Form!ReportDateRange!Ending Order Date.


2) It should be FORMS!Report.. (FORMS with an 'S')

3) Is it [Beginning Order Date] or [Beginning Date]?

I never use spaces in object names (no one see them and spaces makes it
a lot harder when you have to use VB or SQL). Because of the spaces, you
might need to use quotes or brackets.

Maybe not the problems, but something to check...

--
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hi Chris

Thanks for all your help. Sorry I posted twice yesterday . I just wanted to
give you an update the latest changes get the records in SQL and on the
report now.

The only problem I have is if I run the report the form comes up and I type
in the beginning and ending date. Then I get another 2 prompt : enter
parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Any help is appreciated


:



SELECT DSYD.DriverID
,DD1.FirstName & " " & DD1.LastName AS Name
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory
,DSYD.NumOfBreaks AS TotalBreak
,DSYD.DailyHours
,DSYD.ActualDailyHours
,DSYD.Statutory
,IIf(DSYD.ActualDailyHours > 0
,DSYD.ActualDailyHours - DSYD.DailyHours) AS

ExceptHrs

,Switch(DSYD.Statutory="Y",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Reghrs
,Switch(DSYD.Statutory="N",0
,DSYD.ActualDailyHours>0,DSYD.ActualDailyHours
,DSYD.DailyHours>0,DSYD.DailyHours
,True,0) AS Othrs
FROM DriversDescription AS DD1
INNER JOIN
DriverSchedule_YTD AS DSYD
ON DD1.DriverID = DSYD.DriverID
WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date
Range]![Beginning Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date

Range]![Ending

Order Date]))
ORDER BY DSYD.DriverID
,DSYD.SchWorkDate
,DSYD.[Route#]
,DSYD.RouteCategory;


Hi Chris,

Thanks again for your response. I made all the changes you suggested

and

Now the calculations work ok.

I still have the weird problem of skipping some records in the SQL.

This is the data in the YTD file:

DriverID SchWorkDate Route. RouteCategory NumOfBreaks

DailyHours

7091 2/3/2005 003-001-S PM 0 6.51
7965 2/3/2005 099-035-N PM 0 6.08
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17
8287 2/3/2005 003-001-N AM 0 7.83
8287 2/3/2005 003-001-S AM 0 8.17

I enter the following dates on the form : Starting Date : 01/02/04

and

Ending date 02/03/05

The result is only 2 record show up on the report and in SQL the

same 2

records

7965 2/3/2005 099-035-N PM 0 6.08

7091 2/3/2005 003-001-S PM 0 6.51

This function worked @ one point but I am not sure what is happening

here.

Any help is much appreciated. Does the input file have to be sorted

before

it hits the form with the start and end date ?

dskanes,

I *think* I see something that may be causing problems:

WHERE (((DSYD.SchWorkDate) >= [forms]![Report Date Range]![Beginning
Order Date]
AND (DSYD.SchWorkDate) <= [forms]![Report Date Range]![Ending
Order Date]))

Switch the above to:

WHERE (((DSYD.SchWorkDate) >= CDate([forms]![Report Date
Range]![Beginning Order Date])
AND (DSYD.SchWorkDate) <= CDate([forms]![Report Date
Range]![Ending Order Date])))


Let me know if that works.


Sincerely,

Chris O.

Hi Chris

Thanks for all your help.

Tried the latest sql changes but no luck. Then ran the SQL and sql picks up
the 2 records only.

If I run the report the form comes up and type in the beginning and ending
date.
Then I get another 2 prompt : enter parameter value
Form!ReportDateRange!Beginning Date and Form!ReportDateRange! Ending Order
Date.

Then on the report I get the same 2 records -7965 and 7091.

Not Sure why the dates have to be entered twice ?

Thanks for your time and please let me know if you need further details
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Expression Builder and Update Query 1
Query Key 3
Modifying subform's underlying SQL statement 2
Help with expression builder in a Query to retrieve wanted dates 2
query syntax error 1
Error in Expression Builder query... 12
Same query string works in builder but not in code??? 1
Query builder enclosing fieldnames in quotes 3

Top