Access Query Greater and Less than and Date Field

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184
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.010.00
0.010.00
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,268
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 3, 2016
Messages
184
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

Well-known Member
Joined
Jun 3, 2015
Messages
3,268
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 3, 2016
Messages
184

ADVERTISEMENT

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

Well-known Member
Joined
Jun 3, 2015
Messages
3,268
Office Version
  1. 365
Platform
  1. Windows
All you've said is 'it doesn't work' which means what? No results? Error? Something else? Did you try what I suggested?
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184

ADVERTISEMENT

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

Board Regular
Joined
Jun 3, 2016
Messages
184
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

Well-known Member
Joined
Jun 3, 2015
Messages
3,268
Office Version
  1. 365
Platform
  1. Windows
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
SQL:
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,394
Office Version
  1. 365
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,466
Messages
5,831,791
Members
430,087
Latest member
meagerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top