Access Query Greater and Less than and Date Field

[Mac1206]

I have a Access query that is pulling from a Form Date, my issue is I can pull in the Balance To Remit Before < Date but I can't seem to pull in the Balance To Retain After > Date and this is coming from the NetValue Field example....

The Date is pulling from my [Forms]![DCVN_TERM_ReportByDate]![FromDt] Field.

My other issue is how do I use the Balance To Retain: IIF(Nz([NetValue],"",0) if the field is blank, I need to show 0.00

Balance to Remit (Cash received BEFORE DCNV/TERM date)	Balance to Retain (Cash received AFTER DCNV/TERM date)
0.01	0.00
0.01	0.00

 

[Micron]

1st issue - try adding a day (the end date +1) and see what you get. I suspect your date field has no time values other than the default, which is 00:00:00 thus you will get nothing after midnight on the provided date. There are work around for this.
2nd issue - it looks like your NetValue field is text, not a number. If true, change the ,"",0) to ,"","0.00") I'm assuming that your balance field is really the NetValue values.

 

[Mac1206]

1st issue - try adding a day (the end date +1) and see what you get. I suspect your date field has no time values other than the default, which is 00:00:00 thus you will get nothing after midnight on the provided date. There are work around for this.
2nd issue - it looks like your NetValue field is text, not a number. If true, change the ,"",0) to ,"","0.00") I'm assuming that your balance field is really the NetValue values.

Thanks Micron for the quick response:
Do I add it as such: > [Forms]![DCVN_TERM_ReportByDate]![FromDt]+1

The 1st one is <[Forms]![DCVN_TERM_ReportByDate]![FromDt]

 

[Micron]

You could try it and find out easily enough? But I thought the issue was around the end date, not the from date (FromDt)?

I can't seem to pull in the Balance To Retain After > Date

I might be misinterpreting, thinking that you have 2 dates, one for everything after a date and one for everything before a different date. You only have one date?

 

[Mac1206]

This is the Query which works until I put the Greater Than Date > in:

SELECT
rs_NetValue.TransactionDate, rs_NetValue.TransactionQuarter,
ml_tblPlans.DeconversionDate AS [DCNV or TERM], ml_tblPlans.Status, rs_NetValue.NetShare AS [Balance To Remit],
Nz([rs_NetValue].[NetShare],0) AS [Balance To Retain]

FROM ((ml_tblPlans INNER JOIN Tbl_PlanID ON ml_tblPlans.PlanID = Tbl_PlanID.PlanID)
INNER JOIN ml_tblCustodianTrustAcct ON ml_tblPlans.PlanID = ml_tblCustodianTrustAcct.PlanID)
INNER JOIN rs_NetValue ON ml_tblCustodianTrustAcct.CustodianTrustAccount = rs_NetValue.CustodianTrustAccount
WHERE (((ml_tblPlans.DeconversionDate)=[Forms]![DCVN_TERM_ReportByDate]![FromDt])
AND ((ml_tblPlans.Status)="Deconversion")
AND ((rs_NetValue.NetShare)<[Forms]![DCVN_TERM_ReportByDate]![FromDt])
AND ((Nz(rs_NetValue.NetShare,0))>[Forms]![DCVN_TERM_ReportByDate]![FromDt]+1))
ORDER BY rs_NetValue.TransactionDate;

 

[Micron]

All you've said is 'it doesn't work' which means what? No results? Error? Something else? Did you try what I suggested?

 

[Mac1206]

The netvalue field is currency and when the highlighted line is added, I get no data at all and yes I tried what you suggested, thanks but it didn't work....

 

[Mac1206]

Could it be I'm attempting to use the same field twice within the query rs_NetValue.NetShare and using different criteria???

((rs_NetValue.NetShare)<[Forms]![DCVN_TERM_ReportByDate]![FromDt]) AS [Balance To Remit]
((rs_NetValue.NetShare)>[Forms]![DCVN_TERM_ReportByDate]![FromDt]) AS [Balance To Retain]

 

[Micron]

That's the part that's not making sense to me, not that it couldn't work. Basically if you entered 01/01/2021 in the form field, you are asking for all records that are not equal to that date. Rather they are less than AND greater than, but not that date. That is what you're after?

I suggest you copy this query and first replace form references with actual criteria values that you expect would fit the bill. F'rinstance

WHERE (((ml_tblPlans.DeconversionDate)=#01/01/2021# 
AND ((ml_tblPlans.Status)="Deconversion")
AND ((rs_NetValue.NetShare)<#08/15/2021#)
AND ((Nz(rs_NetValue.NetShare,0))>#08/15/2021#))
ORDER BY rs_NetValue.TransactionDate;

Do that for any form field reference, use the proper dates for your data and see what you get. If nothing, scale back by removing all criteria and just return whatever those fields contain. When you get data, add criteria one field at a time. When it stops working, you've found criteria that does not gel with whatever else you've got thus far. You are using equal joins, so I don't suspect that is an issue unless those relationships are not valid.

 

[jackd]

I recommend that you show some data (input and expected output) and walk us through your intended logic to achieve your expected result.
You are showing us how you have done/tried to do something, but we need more details about that something to clarify all communications.
Good luck.