SQL Statement - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SQL Statement

Dwight Arroyo

3 years ago

I am needing help with a SQL statement. I have a database of customers that visit the store from time to time and each time they visit, the date is recorded.

I would like to build an SQL statement where I can see all customers who have NOT visited my store within the last 3 months.

I know this might involve the operator in SQL "between" in some way, but I am not sure.

I know basic SQL (SELECT, FROM, WHERE, LIKE or =) but this particular question is beyond my knowledge and I would so greatly appreciate any help regarding this issue.

If there are any videos by Richard explaining this, please, by all means provide the links.

Otherwise, thank you all in advance for your help.

Dwight

	Richard Rost @Reply 3 years ago
Is the date they last visited the store a single field in your customer table, or is it a new record in a related table (like my contacts table)?

	Dwight Arroyo OP @Reply 3 years ago
The date is a single field in the customer table.

Dwight Arroyo OP @Reply

3 years ago

Maybe I should re-phrase my reply, the date the customer visits the store is always a new record in the customer
table. This table only lists the visits of customers to the store. Each time a customer visits, a new record is created with the
current date. I would like to create an SQL statement that takes the current date I am running this SQL statement, search
the table for all customers who have NOT visited my store 3 months prior from the current date I am running the query. I hope this helps a bit. I am notorious for not explaining myself the way I should. Thank you for your patience.

	Kevin Yip @Reply 3 years ago
Richard has a video on finding customers who have not ordered certain items. You may adapt it to your purpose: https://www.youtube.com/watch?v=A_cm9F_9lMk Basically, you need two queries: one to find customers who HAVE visited your store, and a second query to invert that result to find the non-visitors.

	Alex Hedley @Reply 3 years ago
Have Not Ordered

Dwight Arroyo OP @Reply

3 years ago

I have done much research and still cannot find any answer as I don't want to bother Richard since I know he is super busy. The only statement I finally concluded with was the following:

SELECT * FROM ClientSessions WHERE ProcedureDate < DATEADD(month, -3, GETDATE())

However, this statement does not work either. I might be missing something here, I don't know.

Again, any help pointing in this direction will be greatly appreciated.

Thank you.

Dwight

	Dwight Arroyo OP @Reply 3 years ago
Simple logic of what I need could be: Need to find all customers who have NOT visited my store since 3 months ago from the current date using the ProcedureDate field.

	Dwight Arroyo OP @Reply 3 years ago
I ran this SQL statement in ACCESS and it returned the following error: Undefined function 'GETDATE' in expression

	Kevin Robertson @Reply 3 years ago
`ProcedureDate < DateAdd("m", -3, Date())` DateAdd

	Dwight Arroyo OP @Reply 3 years ago
That is the CODE I needed. It worked!!!!! Thank you so much Kevin! Is there any way I can tip you besides Paypal? My Paypal account is totally messed up. If I send anything it will get lost and you will never get it.

Dwight Arroyo OP @Reply

3 years ago

This is wierd (for me at least). I have created a Visual Basic Application using VB in Visual Studio 2017. I created an SQL query form where the user can type in any SQL query. Your solution worked fine when I typed in the code "SELECT * FROM ClientSessions WHERE ProcedureDate < DateAdd("m", -3, Date())". However, when I use this same code in the editor to attach this sub-routine to a button, I get an error that says "End of statement expected" and the cursor jumps me to the "m" parameter - totally confused. Please help.

	Kevin Robertson @Reply 3 years ago
The DateAdd function is slightly different in VB.NET. `ProcedureDate < DateAdd(DateInterval.Month, -3, Today())`

	Dwight Arroyo OP @Reply 3 years ago
I think I came up with a solution Kevin, I changed the quotes to single quotes and it worked both ways. I am not an expert at this but I do know that SQL gets picky with quotes and single quotes many times. Thank you for your solution nonetheless! Please let me know if there is any other way I can send you a tip to your tip jar. Thank you again!

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

Next Unseen