What function to use

[JaneMore]

I am not sure what function to use for an Access query but I want to say something like this

If membership expire date has past

 

[Norie]

Do you need a function for that, wouldn't the more than operator sufffice?

Expired:Iif([MemberShipExpireData]>Date, 'Expired', 'Not expired')

 

[xenou]

another option (with an Access Query) is to use a WHERE clause:

SELECT * FROM Table1 WHERE [MemberShipExpireDate] < Date()

 

[JaneMore]

I am sorry my post was cut off for some reason

What I was trying to write in Pseudocode was

If membership expire date has pasted print "Inactive" else "Active" but if Denied or
terminated then print "other"

 

[Norie]

Where would Inactive/Active/Other appear and where would Denied/Terminated be indicated?

 

[JaneMore]

Where would Inactive/Active/Other appear and where would Denied/Terminated be indicated?

Hi Norie,

I am building query in access so that I can export the results in a set format to a 3rd party application. I need to have a field that defines the membership status as either "Active, Inactive, Other" to display that in the export I have to query a number of fields.

FieldName	FieldType
Renewal Date	Date/Time
Denied	Boolean
Terminated	Boolean

<tbody>
</tbody>

These are the fields I hope to query, I can get the date by doing < Date() on "Renewal Date" and if it has past then I know they are "Inactive" but then I have to take into account if the member might have a YES in either the Denied or Terminated field to print them with a status of "Other" other wise if the renewal date is in the future then I just print Active.

 

[xenou]

A nested IIF would work. Also Switch (Shown here with a plug in case nothing matches -- which I think could only happen if renewal date were Null):

IIF([Denied] = True OR [Terminated] = True, "Other", IIF([Renewal Date] >= Date(), "Active", "Inactive"))
SWITCH([Denied] = True OR [Terminated] = True, "Other", [Renewal Date] >= Date(), "Active", [Renewal Date] < Date(), "Inactive", True, "Unknown")

 

[JaneMore]

A nested IIF would work. Also Switch (Shown here with a plug in case nothing matches -- which I think could only happen if renewal date were Null):

IIF([Denied] = True OR [Terminated] = True, "Other", IIF([Renewal Date] >= Date(), "Active", "Inactive"))
SWITCH([Denied] = True OR [Terminated] = True, "Other", [Renewal Date] >= Date(), "Active", [Renewal Date] < Date(), "Inactive", True, "Unknown")

Thank you this was just what I was looking for thanks for all the help

 

[JaneMore]

Sorry to post again on an old thread but wanted to say the custom query for the export to excel that this board and members helped me write is working great. Each week the data is exported out to excel using the custom query.

The only issue is we are only interested in "new" records and we manually delete the older records from the excel export that we imported the week before. We know the record number id each week of where the records start and I could just write.

WHERE ID > 13

But as I am not the one that aways runs the query is there any way to popup a window to ask what record to export from ?

 

[xenou]

Access allows you to create parameterized queries.
See here:
MS Access 2003: Create a query that accepts Start date and End date as parameters

(but instead of dates you will be using numbers so just adjust for that).