PerDiem Lookup - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

PerDiem Lookup

Walter Hamilton

4 years ago

I'm trying to implement a tool that will allow a user to determine the PerDiem and Lodging rates base off the selection of State and City as well as the start and end date of the trip.

I imported the latest data from the DFAS website into a new table, RatesConusT. I also create a query and tried to filter the list based off the start date and end date of the travle. I haven't been able to get ot to correctly filter the results.

I've included a screenshot of the query and the criteria that is set.

The query does reduce the number of devices, but there are several duplicates.

Any suggestions to help get everything working

	Walter Hamilton OP @Reply 4 years ago

	Alex Hedley @Reply 4 years ago
If you hardcoded the inputs does it work?

Walter Hamilton OP @Reply

4 years ago

Alex - I tried hard coding the dates in the Query but it still isn't working. I switched to using DatePart because the Season Begin and Season End dates in the lookup table are listed as DD-MMM. Clicking on those fields in the table shows them as all being in 2021. So I need to just compare the Day and Month of the StartDate and EndDate to the SeasonBegin and SeasonEnd. I tried using:

<DatePart("m",#3/3/2022#)

Do I need to specify DatePart of the SeasonBegin and SeasonEnd in the criteria as well?

	Alex Hedley @Reply 4 years ago
Yes Can I see the data types of your Fields in RatesConsusT?

	Richard Rost @Reply 4 years ago
Can I see a sample of the data?

Walter Hamilton OP @Reply

4 years ago

Alex/Richard - Please forgive the slow response. I had the rare opportunity to unplug for a few days and I took advantage of it. I'll upload a screenshot of the some of the sample data as well as the table configuration. The intent is for a user to select the Country, State, City or County or even Zip Code the start and end date and the system will calculate everything. I just need to figure out how to select the correct rates for locations that have different rates depending on the time of year. The SeasonBegin and SeasonEnd fields show the year, but these should just take into account the Day and Month as long as the EffectiveDate in <=Date().

	Walter Hamilton OP @Reply 4 years ago

	Walter Hamilton OP @Reply 4 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View