QBF Help

[nhtraven]

Hi,

This is what i want to do. I want to allow users to set up any query on all fields in two tables. I have never tried the QBF before. I have the sql code needed but am wondering if it will work for a total of 15 fields. Not all fields will be queried at once, maybe 3 criterias at a time.
Is this doable. I have started on it, it is a BUNCH of code. I dont want to invest a lot of time in this if it is not doable. The client needs this asap.


also, in this code that i have seen for QBF they dont have the table name before the field name. I am wondering if this code would be correct:

where = where & " and [tblInput].[parcelno] = ' " + Me![parcelno]

I need to specify which table the field is coming from.


Thanks = )
Raven

 

[tlbroadbent]

It is doable as long as you can write the code properly to create the query. Your code snippet looks like it should work. However, I question the need for the single quote. The single quote will not work if the column is numeric. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[nhtraven]

Terry,

ParcelNo is a text field >>> not always a numeric number for this field and i think the single quote is for text field. Is this correct?

 

[tlbroadbent]

Correct - single quote is right. The column name confused me. I just wanted to be sure you understood that the quote wasn't needed for numeric columns. Good luck. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cmmrfrds]

Here is an example of reading the fields collection for a table. One of the elements in the fields collection is the data type. You can check the data type to determine whether to use quotes around the variable. I assume you will string together up to 3 criteria in your where clause, such as, fld1 char, fld2 numeric, fld3 char, so your where clause would look like this
where fld1 = "critera1" and fld2 = 2 and fld3 = "criteria3"
after you built the where clause string.

Function GetFieldNames(theTable As String) As Long
Dim SqlString As String, RSMT As New Recordset, cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim indx As Integer
''Open the table and the fields are accessible
RSMT.Open theTable, cnn, adOpenStatic, adLockReadOnly

For indx = 0 To (RSMT.Fields.Count - 1)
Debug.Print "field Names = "; RSMT.Fields(indx).Name
Debug.Print "field Type = "; RSMT.Fields(indx).type
Next '-- end for

End Function

Do a Control G after running the function and you will see a list of your field names and data types.