Question on Access 2010 Query

[gildeb]

Hi I need help on a query.

For example in my Table I have (Fund Code, Date, Type A and Type B)
If i need to pick an Item based on date.
Under Item Code i have typed criteria [Enter Item code]
Under date [Enter date]

So each fund code in the table is entered on a monthly basis (month end)

So some Fund code might have started 2/15/2013 some may have started 1/15/2015

So if date chosen is 2/28/2015 for an item which started on 1/15/2015 then it should show data from Type B or if any item started since one year or more should pick data from Type A

So basically this would be a IF statement in excel, though i want to show in a query.

Any help would be greatly appreciated.
G

 

[Joe4]

It might be helpful if you could post an actual example, so we can see exactly what your data looks like and your expected result.

 

[gildeb]

Hi Joe4

For example sake my table is as below: (note: I have not typed in the consecutive month end dates for john from 3/31/2013 to 2/28/2015 to keep example short)
Name Date Data A Data B
John 1/31/2013 10 15
John 2/28/2013 20 25
John 3/31/2015 30 35
Mary 1/31/2015 5 25
Mary 2/28/2015 10 35
Mary 3/31/2015 15 45

On the query criteria under "DATE" I would type Like [Enter date mm/dd/yyyy] & "*"
Under "Name" it would be Like [Enter Name] & "*"

So if I query for "John" and enter 3/31/2015 the result should pick up from Data A which is as of 3/31/2015 is 30 under Data A and for "Mary" for the same date should pick up 45 from Data B
The rule here is if a person start date is => than one year from input date should pick up from Data A and if less than one year then from Data B
Start date is always from a month end date and and input date is also a month end date.

Appreciate any help on the above.
Thanks

 

[Joe4]

A few things:

When naming fields, objects, or variables, you should follow the following rules:
- Do not used reserved words, which include function names, method names, property names, data types, etc. So "Name" and "Date" are bad choices, and can let to confusion and unexpected errors.
- Do not use anything other than numbers, letters, and underscores in field names. Spaces and special characters like "#" should be avoided.
- Do not start the name with a number.
If you follow these rules, you will avoid many issues now (and later, if you ever need to upgrade to SQL).

Now, on to your question.
The first thing I would do is add a calculated field in your query to return the StartDate of each person on every record. Assuming that your date field is in date format (and is not text/string), we should be able to use the DMIN function, along with the CDATE function (since DMIN returns the value as a String).
The formula would look something like:

StartDate: CDate(DMin("DateField","TableName","NameField='" & [NameField] & "'"))

For more on the DMIN and CDATE functions, see:
MS Access: DMin Function
https://support.office.com/en-ca/article/DMin-DMax-Functions-f5c6de87-8752-4f29-ab4b-c554053d299f
MS Access: CDate Function

Now here comes the tricky part. It appears that you want to do math on the date parameter that you are entering (to see if it is more than one year from the Start Date). I don't think you can easily capture that parameter and do calculations on it like that. What I would recommend doing is capturing those parameters in a one record table (can be entered via a Form that is bound to this one record table that doesn't allow any new records to be added to it or deleted, you can only edit the one existing record). You could then use that table in your query to get the date parameter being entered in, and then use it in calculations, specifically subtracting the Start Date from it. You can then use another calculated field (an IIF statement) to check the number of days, and decide whether to return Data A or Data B.

 

[gildeb]

Thanks Joe4 for your reply. Will take note of your advise on the rules.

What doe the "NameField='" refer to because this is without the square bracket.
Thanks

 

[Joe4]

What doe the "NameField='" refer to because this is without the square bracket.

Simply replace "NameField" with the name of the field that you called "Name" in your example (in both places).

The descriptions/links on the DMIN function explain each argument of that function. The last one is the criteria. Basically, you are looking up in your table under the "Name" field where the value is equal to the "Name" field on that particular record.