help in building a dynamic query

  • Thread starter Thread starter george
  • Start date Start date
G

george

My question refers to article # 304302 of the knowledge
base having to do with building a dynamic query with
criteria from a search form.
In the article, inside the code, is mentioned
BuildCriteria(.Name, dbText, .Text). Where does each of
the three parameters refer to? I tried to apply the code
to my situation but the SQL query that resulted from the
coding looked something like this: "Select * from Clients
Where [SMITH] and [JOHN]" -- in other words it didn't get
to include the "LName = " and "FName= " fields (the fields
where SMITH and JOHN come from). Anybody having an idea
why is this happening? Thanks in advance.
 
BuildCriteria(NameOfCriteria?,variabletype,wherestring)

variabletype = dbtext,....
wherestring = LName = ... and FName =...

It's in the Access-Help
 
thanks gadalf
I have already downloaded the generic code from the
article I mentioned and applied it as it is in the
Northwind database and it works. However when I try to
apply it to my database it behaves like I said before.
Since the Buildcriteria part is generic how come it
doesn't work in my db?
-----Original Message-----
BuildCriteria(NameOfCriteria?,variabletype,wherestring)

variabletype = dbtext,....
wherestring = LName = ... and FName =...

It's in the Access-Help
-----Original Message-----
My question refers to article # 304302 of the knowledge
base having to do with building a dynamic query with
criteria from a search form.
In the article, inside the code, is mentioned
BuildCriteria(.Name, dbText, .Text). Where does each of
the three parameters refer to? I tried to apply the code
to my situation but the SQL query that resulted from the
coding looked something like this: "Select * from Clients
Where [SMITH] and [JOHN]" -- in other words it didn't get
to include the "LName = " and "FName= " fields (the fields
where SMITH and JOHN come from). Anybody having an idea
why is this happening? Thanks in advance.

.
Click to expand...
.
Click to expand...
 
Hi George,

It looks like the first argument of the BuildCriteria
function should be a field and not a control property. In
other words, the code looks wrong and should look like
this:

BuildCriteria(FieldName, dbText, .Text)

But I really don't see how this could work for multiple
criterium since there are no operators?? Not too familiar
with this function. If you want, I've got some code that
does builds a SQL statement on the fly from a "report
criteria" form.

Regards,
Jen
-----Original Message-----
thanks gadalf
I have already downloaded the generic code from the
article I mentioned and applied it as it is in the
Northwind database and it works. However when I try to
apply it to my database it behaves like I said before.
Since the Buildcriteria part is generic how come it
doesn't work in my db?
-----Original Message-----
BuildCriteria(NameOfCriteria?,variabletype,wherestring)

variabletype = dbtext,....
wherestring = LName = ... and FName =...

It's in the Access-Help
-----Original Message-----
My question refers to article # 304302 of the knowledge
base having to do with building a dynamic query with
criteria from a search form.
In the article, inside the code, is mentioned
BuildCriteria(.Name, dbText, .Text). Where does each of
the three parameters refer to? I tried to apply the code
to my situation but the SQL query that resulted from the
coding looked something like this: "Select * from Clients
Where [SMITH] and [JOHN]" -- in other words it didn't get
to include the "LName = " and "FName= " fields (the fields
where SMITH and JOHN come from). Anybody having an idea
why is this happening? Thanks in advance.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Try opening the debug window, and start playing with the buildcritain
function.

The function only allows ONE field condition at a time.

So, for example if we wanted to look for someone with a lastname of Smith,
or Jones. The sql would be:

where LastName = 'smith' or LastName = 'Jones'

Thus, you could use buildcritia to make this process easyer.

? buildcriteria("LastName",dbText," = 'Smith' or 'Jones'")
resutls in:

LastName='Smith' Or LastName='Jones'

Note how the condition is correctly built (the lastname field is REPEATED
for you!). It is actually very handy for dates.

? buildcriteria("SomeDate",dbDate," between 01/01/03 and 01/20/03")
Results in:

SomeDate Between #1/1/2003# And #1/20/2003#

To create the above string in code, you would have to use:

dtStart = #01/01/03#
dtEnd = #01/20/03#

strCond = "SomeDate between #" & format(dtStart,"mm/dd/yyyy") & "# and #" &
format(dtEnd,"mm/dd/yyyy") & "#"

You can see how messy it becomes to create conditions. With buildcritara,
the above becomes:

strCond = buildcriteria("SomeDate",dbDate," between " & dtstart & " and " &
dtend)

So, you search form would be (assuming you have a text box on the seach form
called txtFirst, and txtLast:

dim strWhere as string

if isnull(txtFirst) = False then
strWhere = BuildCriteria("LName",dbText," = " & txtFirst)
endif

if isnull(txtLast) = False then

if strWhere <> "" then
strWhere = strWhere & " and "
endif

strWhere = strWhere & BuildCriteria("FName",dbText," = " & txtLast)
endif

At this point you have a correctly formed condition for sql. You can open
your report with these conditions:

docmd.openform "your form",,,strwhere

Or, you can open a report based on these conditions:
docmd.openreport "your report",,,strWhere

In fact, I use the exact idea all the time. The nice thing here is that you
can thus REMOVE ALL conditions from the query for the report, or form. (no
query prompts are required anymore, and that means that each query or reprot
is NOT attached to some cidtion that exists in a form).

Take a look at the following un-bound prompt forms, they ALL use the above
idea:

http://www.attcanada.net/~kallal.msn/Search/index.html
 
Back
Top