Search form, building complex SQL string with VBA (1 Viewer)

[kloklo]

I'm trying to adapt Martin Green's search form code, making SQL and VBA to work together using ADO.
It's no problem when it comes to combo boxes, list boxes and exact matches, but I have been struggling for days with Like and Between. I'm not sure how to build criteria, and how to compose SQL string. My last try is zipped and attached to this post. I'm totaly exhausted from trying and this is the first time ever I couldn't solve my problem by reading other people posts and solved examples. I wouldn't bother you without trying to do it on my own first.

Database has FE and BE, made in Access 2007. I stripped all unnecessary objects from FE, leaving only one form and queries involved in problem.

It is list of Croatian newspapers. I need to make it user friendly for colleagues who doesn't know how to build queries.They need to enter parameters by which they will choose newspapers to send email notifications about our activities.

Translation of fields:

Županija – district
Specijalnost medija – media specialty
Utjecaj medija – media influence
Naziv medija – media name
Naklada, min and max – print copies, min and max

Thank you very, very much in advance for your help, because if this will be solved I will have my first vacation ever without telephone calls from work every two days

 

[JHB]

Try it now:

 

[kloklo]

Thank you, JHB, thank you very much!
I'll try it out and let you know if it works now

 

[kloklo]

Well, after some modifications of your code, JHB, search for part of Media name is working now...jipiiiii Thank you!

Min/max for Naklada (nuber of copies) also works when values from unbound fields are inserted directly into the SQL statement...

What I'm trying to do now is to put these values into string and check if the field is empty (like everything else is checked).

And, I tried to put some date criteria. But, something is wrong with that too. Why? I used proper delimiters for dates...I think... Perhaps European format (dd/mm/yyyy) is causing trouble?

Access trows message that my Output query already exists, and highlits Line 40 in the code (part when query is build if it doesn't exists).

I must ask for your help again.
Database is attached.
Mayday, Mayday, Mayday, my Self-esteem Ship is about to go down Please help!

 

[JHB]

Have you resolved the problems?

 

[kloklo]

No luck :banghead:

I think that maybe I'm doing something wrong when building criteria for these last two strings?

 

[pr2-eugin]

I would like to help, but I am not sure what I am looking at.. There are two ACCDB files inside.. And I am not sure which Form or Query I am looking at too.. So if you could either show the offending Code/explain what I have to look at, I will try my best..

 

[kloklo]

Problem is in my front end called "ADREMAFE-2", form name is "frmEmailovi".

Everything worked fine until I added strings for searching between NakladaMin and NakladaMax (number field), and PocetakMin and PocetakMax (date field in format dd/mm/yyyy)

'Build criteria string for Naklada
strNakladaMin = Me.txtNakladaMin.Value
strNakladaMax = Me.txtNakladaMax.Value
If Len(strNakladaMin) = 0 Then
strNakladaMin = "'*'"
End If
If Len(strNakladaMax) = 0 Then
strNakladaMax = "'*'"
End If

' Build criteria string for MedijPocetak
strPocetakMin = Me.txtPocetakMin.Value
strPocetakMax = Me.txtPocetakMax.Value
If Len(strPocetakMin) = 0 Then
strNakladaMin = "'*'"
End If
If Len(strPocetakMax) = 0 Then
strNakladaMax = "'*'"
End If

Final SQL statement:

strSQL = "SELECT * FROM qryEmailoviBaza " _
& "WHERE qryEmailoviBaza.[Zupanija] " & strZupanije _
& strSpecijalnostMedijaCondition & "qryEmailoviBaza.[MedijSpecijalnost1] " & strSpecijalnostMedija _
& strUtjecajMedijaCondition & "qryEmailoviBaza.[MedijUtjecajMedija] " & strUtjecajMedija _
& strMedijNazivCondition & "qryEmailoviBaza.[MedijNaziv] " & strMedijNaziv & "" _
& strNakladaCondition & "qryEmailoviBaza.Naklada2013Plan BETWEEN " & strNakladaMin & " AND " & strNakladaMax & "" _
& strPocetakCondition & "qryEmailoviBaza.[MedijPocetak] BETWEEN #" & strPocetakMin & "# AND #" & strPocetakMax & "#;"

 

[kloklo]

When I click "OK", It gives me message:
Object "qryEmailoviOutput" already exisits.

...and takes me to this line of code:

cat.Views.Append "qryEmailoviOutput", cmd

 

[pr2-eugin]

Okay, you are checking the Catalog for the existence of the Query but you have not checked the DB.. Delete the Query that you have, it should be fine..

 

[kloklo]

When I delete the query and run search again, it gives me syntax error. I attached PrintScreen of it.
And takees me to this line:

DoCmd.OpenQuery "qryEmailoviOutput"

In the error message it looks like my SQL statement is cut of at the and?

I checked names of everything, It seems OK. In immediate window it also looks fine. I'm confused.

 

[pr2-eugin]

Could you paste the Query generated? Syntax error has nothing to do with deleting the Query.. There is problem generating the Query..

The Error shown has a loose hanging qryEmail at the end..

 

[kloklo]

Here it is:

SELECT * FROM qryEmailoviBaza WHERE qryEmailoviBaza.[Zupanija] Like '*' AND qryEmailoviBaza.[MedijSpecijalnost1] Like '*' AND qryEmailoviBaza.[MedijUtjecajMedija] Like '*' AND qryEmailoviBaza.[MedijNaziv] LIKE '*novine*' AND qryEmailoviBaza.Naklada2013Plan BETWEEN 0 AND 200000 AND qryEmailoviBaza.[MedijPocetak] BETWEEN #1.1.1930.# AND #1.1.2012.#;

 

[pr2-eugin]

I do not think 1.1.1930. is a valid Date.. Try the normal Date Format.. dd/mm/yyyy or mm/dd/yyyy

 

[kloklo]

We are almost there

I did what you suggested and changed last line it the code to this:

strSQL = "SELECT * FROM qryEmailoviBaza " _
& "WHERE qryEmailoviBaza.[Zupanija] " & strZupanije _
& strSpecijalnostMedijaCondition & "qryEmailoviBaza.[MedijSpecijalnost1] " & strSpecijalnostMedija _
& strUtjecajMedijaCondition & "qryEmailoviBaza.[MedijUtjecajMedija] " & strUtjecajMedija _
& strMedijNazivCondition & "qryEmailoviBaza.[MedijNaziv] " & strMedijNaziv & "" _
& strNakladaCondition & "qryEmailoviBaza.[Naklada2013Plan] BETWEEN " & strNakladaMin & " AND " & strNakladaMax & "" _
& strPocetakCondition & "qryEmailoviBaza.[MedijPocetak] BETWEEN #" & Format(strPocetakMin, "mm\/dd\/yyyy") & "# AND #" & Format(strPocetakMax, "mm\/dd\/yyyy") & "#;"

Now search form works when something is selected in Naklada and Početak fields.

But, although there are no empty fields in my underlying query and all data seems valid, If I leave Naklada or Pocetak boxes empty it gives me Invalide use of Null.

Could you, or some other volounteer , look at this criteria. Whole the time I suspect I'm doing something wrong with it. Maybe I shoud use different method than Len function to see if the field on the form is left blank?

'Build criteria string for Naklada
strNakladaMin = Me.txtNakladaMin.Value
strNakladaMax = Me.txtNakladaMax.Value
If Len(strNakladaMin) = 0 Then
strNakladaMin = "'*'"
End If
If Len(strNakladaMax) = 0 Then
strNakladaMax = "'*'"
End If

' Build criteria string for MedijPocetak
strPocetakMin = Me.txtPocetakMin.Value
strPocetakMax = Me.txtPocetakMax.Value

If Len(strPocetakMin) = 0 Then
strNakladaMin = "'*'"
End If
If Len(strPocetakMax) = 0 Then
strNakladaMax = "'*'"
End If

 

[pr2-eugin]

Len is a String manipulation function which does not like the use of Null... So your code should be..

'Build criteria string for Naklada
strNakladaMin = Me.txtNakladaMin
strNakladaMax = Me.txtNakladaMax
If Len(strNakladaMin [COLOR=Red][B]& vbNullString)[/B][/COLOR] = 0 Then
    strNakladaMin = "'*'"
End If
If Len(strNakladaMax [COLOR=Red][B]& vbNullString)[/B][/COLOR] = 0 Then
    strNakladaMax = "'*'"
End If

' Build criteria string for MedijPocetak
strPocetakMin = Me.txtPocetakMin
strPocetakMax = Me.txtPocetakMax

If Len(strPocetakMin [COLOR=Red][B]& vbNullString)[/B][/COLOR] = 0 Then
    strNakladaMin = "'*'"
End If
If Len(strPocetakMax [COLOR=Red][B]& vbNullString)[/B][/COLOR] = 0 Then
    strNakladaMax = "'*'"
End If

 

[kloklo]

Invalide use of null again...

Highlited row is:
strNakladaMin = Me.txtNakladaMin

I changed it to strNakladaMin = Me.txtNakladaMin.Value, but with no effect.

I tried different method:

If IsNull(strNakladaMin) Or strNakladaMin = "" Then
strNakladaMin = "'*'"

This also gives me Invalid use of null and same highlithed row.
Can you think of reason why this is not working?

Thank you both for your help so far

 

[pr2-eugin]

I think you have declared the variables as String.. So use..

strNakladaMin = Nz(Me.txtNakladaMin, vbNullString)

 

[kloklo]

I'm so thankful for your help...but that is not working either...

You know what...damn the code...I'll quit for today and sit in front of TV to watch "Gladiator".

Maybe looking at the half naked, muscular, dirty man will help my little grey cells relax and work better afterwards

 

[JHB]

There are some discrepancies in your code:
Example, (when do you think the IF statement in the below code becomes true?) :

     ' Build criteria string for Medij naziv
   
            strMedijNaziv = "LIKE '*" & Me.txtMedijNaziv.Value & "*'"
          If strMedijNaziv = "" Then
          strMedijNaziv = "Like '*'"
         End If

Then you are using "*" in "BETWEEN" when some of the text controls not have any value. You can't use it on this way.
In the below code you are testing for Me.txtPocetakMin and Me.txtPocetakMax, but setting the variable strNakladaMin and strNakladaMax, which you have set quiet before.

If Len(strNakladaMin) = 0 Then
        strNakladaMin = "'*'"
        End If
      If Len(strNakladaMax) = 0 Then
         strNakladaMax = "'*'"
      End If
       
' Build criteria string for MedijPocetak
    strPocetakMin = Me.txtPocetakMin.Value
    strPocetakMax = Me.txtPocetakMax.Value
   
       If Len(strPocetakMin) = 0 Then
        strNakladaMin = "'*'"
        End If
      If Len(strPocetakMax) = 0 Then
        strNakladaMax = "'*'"
        End If

Then you are using [FONT=&quot]ADOX, it is not giving your any benefit.
I've attached the database in which I've made some correction.


[/FONT]