query doesnot update with new data 2

[GelC]

Good morning everyone,
I need some help from this forum.
I have a search form is built from a qry and the qry bases on a table. For some reason, the qry doesnot get all data but the table.
Can anyone point out my error. Many thanks.
My SQL is below:

SELECT *
FROM tblAllSongs
WHERE (((tblAllSongs.SongNumber) Like "*" & IIf([Forms]![frmSongSearchOptions]![SongNumber]<>"",[Forms]![frmSongSearchOptions]![SongNumber],"*" & "*") & "*") AND ((tblAllSongs.RptYear) Like IIf([Forms]![frmSongSearchOptions]![Year]<>"",[Forms]![frmSongSearchOptions]![Year],"*")) AND ((tblAllSongs.Author) Like "*" & IIf([Forms]![frmSongSearchOptions]![Author]<>"",[Forms]![frmSongSearchOptions]![Author],"*" & "*") & "*") AND ((tblAllSongs.RptMonth) Like IIf([Forms]![frmSongSearchOptions]![Month]<>"",[Forms]![frmSongSearchOptions]![Month],"*")) AND ((tblAllSongs.TypeOfSong) Like IIf([Forms]![frmSongSearchOptions]![TypeOfSong]<>"",[Forms]![frmSongSearchOptions]![TypeOfSong],"*")) AND ((tblAllSongs.SongArea) Like IIf([Forms]![frmSongSearchOptions]![SongArea]<>" ",[Forms]![frmSongSearchOptions]![SongArea],"*")) AND ((tblAllSongs.Title) Like "*" & IIf([Forms]![frmSongSearchOptions]![Title]<>"",[Forms]![frmSongSearchOptions]![Title],"*" & "*") & "*") AND ((tblAllSongs.Keywords) Like "*" & IIf([Forms]![frmSongSearchOptions]![Keywords]<>"",[Forms]![frmSongSearchOptions]![Keywords],"*" & "*") & "*"))
ORDER BY tblAllSongs.SongNumber;

 

[dhookom]

When your form is open and no criteria have been entered, try open the debug window (press Ctrl+G) and enter:

?[Forms]![frmSongSearchOptions]![Month]<>""

If there is no value in a control then you need to use
[Forms]![frmSongSearchOptions]![Month] Is Null
or
IsNull([Forms]![frmSongSearchOptions]![Month])

If you are using the query as the record source of a form or report and all the fields are in the record source, then I suggest you remove all criteria and use the where condition of the DoCmd.Open...


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GelC]

Thanks dhookom for your input.
I tried with following based on your suggestion

Private Sub Search_Click()
On Error GoTo Err_Search_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSongSearchResults"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Search_Click:
    Exit Sub

Err_Search_Click:
    MsgBox err.Description
    Resume Exit_Search_Click
    
End Sub

I run into this problem. The performance of search with "blank criteria" give me only 471 records while there are total of 569 records in my table. Why?
Also, I have removed the "month" from my qry because I do not need it anyway. I think it should not be a problem.
Thanks a bunch

 

[dhookom]

If you have left the criteria in the query then you must understand that if a field like Author is null/blank in your table, it will never match
Like "*"
Also, [tt][red]"*" & "*"[/red][/tt] makes no sense.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

What about this construct ?
SELECT *
FROM tblAllSongs
WHERE (SongNumber Like '*' & [Forms]![frmSongSearchOptions]![SongNumber] & '*' OR [Forms]![frmSongSearchOptions]![SongNumber] & '' = '')
AND (RptYear Like '*' & [Forms]![frmSongSearchOptions]![Year] & '*' OR [Forms]![frmSongSearchOptions]![Year] & '' = '')
AND (...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GelC]

Thanks again.
I'll check those mistakes

 

[dhookom]

PH,
I have never seen the ''='' trick before. This is a nice twist to ... Is Null. Very interesting (made me think).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]