I would like help writing a query to determine when people should follow up on a late shipment
Basically, I have a table that is a log for when issues of different magazines are received. Here are the fields: Title of the magazine Frequency (Monthly, bimonthly, annual, etc) Issue date (the date printed on the magazine) Received Date (The date the item was received) Expected Next Issue Date (Calculated field that figures out when the next issue should come in based on the Received Date and the Frequency) I want a query that, for each title, returns the record for the last issue that was received, but ONLY if it is after the "Expected Next Issue Date" for that record. For example, Time is Biweekly. So If they receive it January 1, and it is now January 15 and the next issue was not received, I want the query to return the record for the issue that was received on January 1, but not any of the issues that were returned before that (so not any issues that were received in December of the previous year). Right now I have a query that does that, but it requires the user to check off old issues as they receive things, which is not ideal. Any ideas on how to write a query that will do what I want?Access Query Expressions not working: Replace, InStr
I am trying to run a relatively simple query and receive a compile error. The two fields involved are both text fields. SELECT Spouse, Replace(Spouse, Last, "") AS Result FROM SimplifiedTable; What this query should do is look at a text Spouse field, and, if the last name is present in it, replace it with blanks. Ideally, I would add an RTrim to the thing to just get the first name of the spouse. I have tried the same query with brackets around the fields, with tableName.fieldname, etcetera. No joy: Compile error. in query expression 'Replace(Spouse, Last, ""'. (It also errors if I try using single quotes -- is there an escape I should be using?) Additionally, I enlisted the assistance of AI and tried to do the same thing using InStr -- Here's a sample of other approaches attempted: SELECT Spouse, IIf(Spouse Like "*" & [Last] & "*", Left(Spouse, InStr(Spouse, [Last]) - 1), Spouse) AS Result FROM SimplifiedTable; SELECT Spouse, IIf(Spouse Like "*" & [Last] & "*", Left(Spouse, Len(Spouse) - Len([Last]) + 1), Spouse) AS Result FROM SimplifiedTable; Each of these queries received the same error message (the quoted text the only differentiator). If I run a select query on just the fields, or concatenate them, no problem. Thanks!
Access 365 Query
I am using Access 365 on Windows 10 PC. My database is in Access 2016 format. I haven't used Access in some time and am perplexed by my difficulty in successfully designing a query. I have a list of members of a legislature. One of the fields is office type; the choices are Legislative Office, Constituency Office or Ministry Office. I have tried multiple options in my Simple Query to obtain a list of Legislative offices. No matter what I've tried, I get blank results with a syntax error message. Among my attempts where "Legislative", "Legislative Office" and "not C" None have been successful. Screenshots attached show table format, query design view and syntax error. Please point me in the right direction. Thanks in advance
