Good morning.
I need to build a query from an inspection table based on the inspection date.

I'm using Allen Brown's search program and I'm trying to modify it so when I put in the dates it will build a query from the qryFireInspection based on the field called Inspdt.

If I'm using the same table then it will work on the dates. Because the qryFireInspection is based on a relationship between tblFire and tblFinspection {one to many} it doesn't appear to work.

This is the code I have so far:



The StrWhere is actually building a query string from all the information so when its finished, it will create a filter.

Its probably something simple that I've over looked.
Thanks for your help.

AfroSheen15 -

What type of format is conJetDate? If your dates are not stored as true dates, then you may need a text delimiter. And the date format would have to be YYYYMMDD or something that would sort properly for the >= operator to work.

If you are using true dates, then you don't need to format them. You are missing the date delimiters, so try this:

StrWhere = StrWhere1 = "select inspdt from qryFireInspection where ([inspdt] >= #" & Me.txtStartDate & "#) AND "

If that doesn't work, let us know what conJetDate is, and how your inspdt is stored...
- Daryl

Thanks for the reply. Here is what conjet is

The problem is likely the two assignment statements - maybe this is a typo?

StrWhere = StrWhere1 = "select inspdt from qryFireInspection where ([inspdt] >= #" & Me.txtStartDate & "#) AND "

maybe should be this:

StrWhere = StrWhere1 & "select inspdt from qryFireInspection where ([inspdt] >= #" & Me.txtStartDate & "#) AND "

or this:

StrWhere = "select inspdt from qryFireInspection where ([inspdt] >= #" & Me.txtStartDate & "#) AND "

Try fixing that so you don't have two equal signs in the statement.

If you are getting an error message, please let us know what it is. Also, if you can add this code:
Debug.Print StrWhere
below the StrWhere = statement, then look in the immediate window to see how Access has built the strWhere, that should clue you in on any mistakes. You can put this statement in a few places as StrWhere is being built.

Hope that helps - if not, copy/paste the statement from the immediate window into your next post, and include any error messages you see.

- Daryl

This is the complete build of the strWhere query.



It said that there was a syntax error in the query.

Sorry, I had the code a little wrong.

The code is pasted as:



I was getting a syntax query expression error

Afrosheen -

Normally we use strWhere to hold just a WHERE clause, and strSQL to hold a SQL statement. It looks like you are putting SQL statements into a WHERE clause. I suspect your strWhere should only look like this:
WHERE ([inspdt] >= #1/1/2011#) AND ([inspdt] < #12/31/2011#) AND

So I would update your StrWhere1 to this:
StrWhere1 = "([inspdt] >= #" & Me.txtStartDate & "#) AND "

Then I assume StrWhere is really a SQL statement. If it is only to hold the WHERE clause, then you need to do the same thing, and remove the text before the WHERE.

Then before you run this, you need to remove the last " AND " from the statement. These are usually added when we have a lot of optional criteria in code to build the WHERE clause. When all the criteria are added, the final " AND " needs to be removed. That statement could look like this:

StrWhere = Left(StrWhere,len(StrWhere)-5)
Print.Debug StrWhere

Give that a go.
- Daryl

It started working until it came to the input box asking me for the field [inspdt]. That is from the table called tblFireInspection. The table the program opens with is called tblFire.

I may not be able to get the results I'm looking for. If that's the case then Ok, I'll deal with it..

Afrosheen -

Post your final full SQL statement (you can use a Debug.Print statement after all the code that builds the SQL). Maybe we can spot the issue.

- Daryl

Edit - I just noticed in my prior post I had Print.Debug instead of Debug.Print - don't know what my fingers were thinking!

Sorry. It willl have to be next week. going out of town this weekend.

Will check back on Monday or Tuesday.

Sorry for getting back so late.
Instead of trying to display information from one table to the next, what are your thoughts of doing a search on the product then display the inspection information in a combo box.

For example: Fire ext. "Baker" has 5 inspection records. On that same line if I click on the combo box then it will show all the information pertaining to Baker.

I don't know if it can be done or not.

Normally if you have a one-to-many relationship, you select the record from the 'one' side (e.g. Baker), and then display the records from the 'many' side in a subform, so that you can see all 5 inspection records.

Does that help?
- Daryl

I believe it does. I already have a inspection subform created so I guess I could click on the fire ext. name and have it open the inspection history subform

Thanks for the help and direction.

Always happy to help!