Missing a parameter in a SQL

[whittam]

I have the following SQL and I am getting Missing Parameter when I am using it can anyone help me with the syntax

Data1.RecordSource = "Select House.HStreet, OInspect.HourBegin, OInspect.HourEnd From House,OInspect Where House.AgentID = " & Agent(cboAgent.ListIndex) & "And OInspect.ODate > " & txtStart.Text & "And OInspect.ODate < " & txtFinish.Text & " Order By OInspect.ODate"

It uses three variables off aform and fields from two tables

Try this:
"Select House.HStreet, OInspect.HourBegin, OInspect.HourEnd From House,OInspect Where House.AgentID = '" & Agent(cboAgent.ListIndex) & "' And OInspect.ODate > '" & txtStart.Text & "' And OInspect.ODate < '" & txtFinish.Text & "' Order By OInspect.ODate"

I guess another problem was that there was no space before the "And"s..

[JHausmann]

It doesn't make much difference on this query (because it's relatively small) but you should get in the habit of using aliases.

Your query:

"Select House.HStreet, OInspect.HourBegin, OInspect.HourEnd From House,OInspect Where House.AgentID = '" & Agent(cboAgent.ListIndex) & "' And OInspect.ODate > '" & txtStart.Text & "' And OInspect.ODate < '" & txtFinish.Text & "' Order By OInspect.ODate"

could be reworked to:

"Select a.HStreet, b.HourBegin, b.HourEnd From House a, OInspect b Where a.AgentID = '" & Agent(cboAgent.ListIndex) & "' And b.ODate > '" & txtStart.Text & "' And b.ODate < '" & txtFinish.Text & "' Order By b.ODate"

Easier to read and it's shorter (you may run into instances later with large queries where you cannot fit it into a string variable if you don't use aliases).

[whittam]

Your right I should I was also wondering whether I could use the Between clause to check for a valid date???

[BruceG]

The "missing parameter" error is due to misspelling a field name from a table involved in your query (i.e., it is interpreting the misspelled field as a parameter variable for which you have not specified a value). Check your field names in the tables and make sure they match up to the names used in your query.

Also, if you are using Access SQL, your should specify the aliases for your table names with the AS clause and enclose the date values in pound (#) signs. And yes, you can use the BETWEEN clause for the dates. For example, you can modify JHausmann's query as follows:

"Select a.HStreet, b.HourBegin, b.HourEnd From House AS a, OInspect AS b Where a.AgentID = '" & Agent(cboAgent.ListIndex) & "' And b.ODate BETWEEN #" & txtStart.Text & "# And #" & txtFinish.Text & "# Order By b.ODate"

The above assumes the user has entered valid dates in txtStart and txtFinish, in mm/dd/yy or mm/dd/yyyy format.

[whittam]

OK so I have put in every version of this SQL and I am still getting missing parameter, I have checked all field names, tried substituting variables and compared it to other SQL's I have written, and though it seems the same something is still wrong
Has anyone got any other suggestions

At the moment the SQL looks like this:"Select a.HStreet, b.HourBegin, b.HourEnd From House AS a, OInspect AS b Where a.AgentID = '" & Agent(cboAgent.ListIndex) & "' And b.ODate BETWEEN #" & txtStart.Text & "# And #" & txtFinish.Text & "# Order By b.ODate"

Just a side note I went into the VisData tool to see if I could find out what was going wrong and entered the following SQL (a variation on the one above using variable names)
Select House.HStreet, OInspect.ODate,OInspect.AgentID From House, OInspect Where (OInspect.ODate Between Start And Finish ) And (OInspect.AgentID =AgentNo) And (House.HouseID = OInspect.HouseID) Order By OInspect.ODate

This works in Visdata so how do I transfer it to the form??????




[Edited by whittam on 08-19-2000 at 07:15 AM]

[JHausmann]

set your SQL statement to a variable, then place a breakpoint on that line that sets the variable's value.

for example
dim sSQL as string

sSQL="Select a.HStreet, b.HourBegin, b.HourEnd From House AS a, OInspect AS b Where a.AgentID = '" & Agent(cboAgent.ListIndex) & "' And b.ODate BETWEEN #" & txtStart.Text & "# And #" & txtFinish.Text & "# Order By b.ODate"

run your program.
when it gets to the break point, press f8 (single step).
in the "immediate window" type ?sSQL
hit return and post the results back here

[whittam]

sSQL shows the following information at the breakpoint :Select a.HStreet, b.HourBegin, b.HourEnd From House AS a, OInspect AS b Where a.AgentID = '2' And b.ODate BETWEEN #8/15/00# And #8/31/00# Order By b.ODate

The error message is still Runtime error 3061 Too few parameters, expected 1

[JHausmann]

Can you now post the fields in House and Oinspect and what their datatypes are?

[whittam]

OK,this is set out in fields,Access DataType:VB Program DataType:
House
HouseID:Long Integer
HStreet:text
HSuburb:Text
HPostCode:Integer
ClientID:long Integer
HType:Text
Value:Currency
Status:Text
SellMode:Text
HDescription:Memo
OInspect
OInspectionID:Long Integer
HouseID:Long Integer
Dateate/Time
HourBeginate/Time:Variant
HourEndate/Time:Variant(When put into a variable)
AgentID:Long Integer:Integer
TakenPlace:Text
Comment:Memo

[AKA]

The House table is missing the AgentID field that you are qualifying on. Is the error in the SQL or table defenition ?

Should you not join the both tables with H.HouseId = OI.HouseID ?

Use better alias than a and b. I always have same alias for same table and defines them as I define the table.

[whittam]

I did have the join in the SQL at one stage and have now replaced it but am still getting the same error message

the present format is as follows:

Select a.HStreet, b.HourBegin, b.HourEnd From House AS a, OInspect AS b Where b.HouseID = a.HouseID and a.AgentID = '2' And b.ODate BETWEEN #8/15/00# And #8/31/00# Order By b.ODate

I guess using more explicit names for the tables would be better but I still normally but the full table name in to help me understand

[AKA]

Did you miss this, I think that is your problem.

The House table is missing the AgentID field that you are qualifying on. Is the error in the SQL or table defenition ?

[JHausmann]

Because AgentID is a number, try:

sSQL="Select a.HStreet, b.HourBegin, b.HourEnd From House AS a, OInspect AS b Where b.AgentID = " & Agent(cboAgent.ListIndex) & " And b.ODate BETWEEN #" & txtStart.Text & "# And #" & txtFinish.Text & "# Order By b.ODate" and a.houseID=b.houseID