Issues running a SQL statement - Microsoft Access Forum


	Back to Access Forum Comments List

Issues running a SQL statement

Jerry Fowler

4 years ago

I have a form (DuesFirstNoticeF) which ran fine as its own form, but when I ended up it became a subform within a 2nd subform within a mainform. So I updated the queryto reflect where the ID (MemID) was coming from to [Forms]![MainForm]![SubForm]!{DuesFirstNoticeF]!MemID and there is where it went wrong. I could not get it to run as it seemed it could get past the 1st subform.
So I thought I would try using a DoCmd.RunSQL within the form itself. so Screenshot 3 is the function and Screenshot 1 is when I press the button it's passing the right ID (Formatted as short text) then I get the error in Screenshot 2. I believe its not in the SQL statement itself but rather in how I have the function setup. But I could very well be wrong. Any additional info you need or questions just ask.

Thanks

	Jerry Fowler OP @Reply 4 years ago

	Jerry Fowler OP @Reply 4 years ago

	Jerry Fowler OP @Reply 4 years ago

	Alex Hedley @Reply 4 years ago
If you add a `Debug.Print SQLTxt` before the run command Copy that from the output window Paste it into a new blank Query window (SQL View) Try and run it What happens?

	Jerry Fowler OP @Reply 4 years ago
Alex, I did as you suggested and when I ran it it asked for the Parameter Value for 'Date'. I'm not asking for a field called 'Date' or for a value of Date.

	Richard Rost @Reply 4 years ago
Is that MemID a Long (AutoNumber)? If so, don't put it inside of quotes. And yeah, you need to simplify that statement. A LOT.

	Richard Rost @Reply 4 years ago

	Richard Rost @Reply 4 years ago
Is [Date] in one of your queries? I'd strongly recommend breaking this down into multiple, smaller queries.

	Kevin Robertson @Reply 4 years ago
I think possibly `Year(Date)` needs to also be outside the quotes.

	Richard Rost @Reply 4 years ago
Most likely. Good catch Kevin.

Jerry Fowler OP @Reply

4 years ago

Thanks Alex, Kevin and Richard you definitely have great questions. First Richard the MemID is not an Autonumber it is actually a string of 8 numbers and I need the leading zeros so I formatted it in the table as Short Text. and Alex and Kevin I do think that the Year(Date) is the issue as when I ran the query as Alex suggested it ask for a Value for Date and I didn't even think of putting a date in. When I did the query ran as it should. So I will first try and take the date out of the quotes and concatenate the string together. Then if that does not work I will try and break up the query into smaller ones. Thanks again you all are a great resource.

Jerry

	Richard Rost @Reply 4 years ago
Yeah, it should look like: `... "WHERE BillingT.DuesYear=" & Year(Date()) & " AND " ...` That will convert to: `... "WHERE BillingT.DuesYear=2022 AND " ...` Before the SQL is evaluated.

	Jerry Fowler OP @Reply 4 years ago
One question before I start work on the SQL statement, then all non text fields should be outside of quotes?

Richard Rost @Reply

4 years ago

The deciding factor is whether or not you're referring to a field that the query can actually access, or if it's a system value, or a field on a form... there are a lot of possibilities, and it doesn't always have to do with the data type. Strings may need to be handled that way too sometimes. I cover a lot more in my SQL Seminar, but with a query as complex as the one you're trying, I think the first step is to simplify it. Break it down into smaller steps, and then slowly add stuff to it and see what works and what doesn't work.

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

Next Unseen