Create report with 2 queries

[Duane Hookom]

Oh yes it does! From what I can see there is absolutely no wrong results, no
error messages, and no issues.

 

[sumana]

Hi,
If we use the operator Val(DCI.DCINo) in the query qtotActEffort, the
result is fine, infact there is no change from the previous results
when we didn't have the Val() prefix.

If we use the same operator in the query Query2, it lists the DCI No
30000 as between 200 and 5000. This is not working fine

Sumana

 

[Duane Hookom]

Could you share some SQL views?

 

[sumana]

What kind of views would you like to see? Are you indicating any SQLs?
I didn't exactly get what you meant, sorry!

Could you share some SQL views?

 

[Duane Hookom]

You can view a query in SQL view. Then copy and paste the SQL into a reply.

Duane Hookom
MS Access MVP

 

[sumana]

the following are the queries in the sql view

qtotActEffort:

SELECT Sum(DCI.ActEffort) AS SumOfActEffort
FROM DCI
WHERE (Val(DCI.DCINo) Between Forms!frmReportInput!txtDCIStart And
Forms!frmReportInput!txtDCIEnd);


Query2:

SELECT DCI.DCINo, DCI.RewEffort, qtotActEffort.SumOfActEffort
FROM DCI, qtotActEffort
WHERE ((Val(DCI.DCINo) Between Forms!frmReportInput!txtDCIStart And
Forms!frmReportInput!txtDCIEnd) And
((DCI.RewEffort)>Forms!frmReportInput!txtRwkVal));


thanks
sumana

You can view a query in SQL view. Then copy and paste the SQL into a reply.

Duane Hookom
MS Access MVP

 

[Duane Hookom]

Are you suggesting that records are displaying in the query where the
numeric value of DCINo is not between the numeric value in txtDCIStart and
txtDCIEnd? How about using the Query->Parameters to define the data type of
your text boxes. Also, is RewEffort text or numeric?

 

[sumana]

Example: txtDCIStart = 200 txtDCIEnd = 5000 RewEffort = 0, then it
lists a record which has DCINo = 30000 which is incorrect.(as you
suggested as a possible bug before in this discussion thread)

I cannot use query parameters coz I have to generate a lot of reports
based on same input. So, it would be best to input all the parameters
once, and allow the user to choose which report to be generated.

RewEffort is numeric(decimal)

Thanks
Sumana

Are you suggesting that records are displaying in the query where the
numeric value of DCINo is not between the numeric value in txtDCIStart and
txtDCIEnd? How about using the Query->Parameters to define the data type of
your text boxes. Also, is RewEffort text or numeric?

 

[Duane Hookom]

I have heard of issues with Decimal type fields. Try:
qtotActEffort:

SELECT Sum(DCI.ActEffort) AS SumOfActEffort
FROM DCI
WHERE (Val(DCI.DCINo) Between Val(Forms!frmReportInput!txtDCIStart) And
Val(Forms!frmReportInput!txtDCIEnd));


Query2:

SELECT DCI.DCINo, DCI.RewEffort, qtotActEffort.SumOfActEffort
FROM DCI, qtotActEffort
WHERE Val(DCI.DCINo) Between Val(Forms!frmReportInput!txtDCIStart) And
Val(Forms!frmReportInput!txtDCIEnd) And
Val(DCI.RewEffort)>Val(Forms!frmReportInput!txtRwkVal);

 

[sumana]

Sorry I replied late, was not working on this for some time.
These solutions what you mentioned are working.
Thanks for everything

Sumana

I have heard of issues with Decimal type fields. Try:
qtotActEffort:

SELECT Sum(DCI.ActEffort) AS SumOfActEffort
FROM DCI
WHERE (Val(DCI.DCINo) Between Val(Forms!frmReportInput!txtDCIStart) And
Val(Forms!frmReportInput!txtDCIEnd));


Query2:

SELECT DCI.DCINo, DCI.RewEffort, qtotActEffort.SumOfActEffort
FROM DCI, qtotActEffort
WHERE Val(DCI.DCINo) Between Val(Forms!frmReportInput!txtDCIStart) And
Val(Forms!frmReportInput!txtDCIEnd) And
Val(DCI.RewEffort)>Val(Forms!frmReportInput!txtRwkVal);