Changing query criteria with VBA

[CJ_London]

You can try reverting to ORs but all as one line in the criteria - which is what I showed you in an earlier post - see attached

 

[andymartnez]

You can try reverting to ORs but all as one line in the criteria - which is what I showed you in an earlier post - see attached

Yes that's how I had it. But the problem still remains that it requires all 3 keyword search textboxes to be filled in, otherwise everything in the table is returned. If you want I can split the db and send you the front-end and see what you can make of it.

EDIT: Also, this is somewhat unrelated, but still in the realm of Access, when I have a report in report view and I click on a section (header, detail, footer, etc.) it highlights the entire section in this gray haze with a yellow border, is there any way to make that not happen? Again, you may want to see the front-end to know what I'm talking about.

 

[CJ_London]

Try this

(like "*" & nz(Forms!search_frm!keyword_1_search) & "*" and len(nz(Forms!search_frm!keyword_1_search))>0) OR....

 

[andymartnez]

You, sir, are a SQL magician. I was thinking about doing something similar to what you posted (using the Nz phrase) but I just couldn't quite figure out how exactly to put it. It worked like a dream!

I went back and thanked all your posts for sticking with me on this one. Thank you so much!

Not to kill the mood, but I posted another question in the "edit" on my last reply, if you wouldn't mind taking a look at that. It's nothing major, it just kinda bugs me that it does that.

 

[CJ_London]

With regards your edit, I am aware of the effect you are talking of but don't know how to eliminate it. It can be useful for continuous reports to 'highlight' a line - you would be better to put this in a new post.

Regret I really only use reports for printing, All my 'form' reports are interactive - drill down/expansion, etc - reason is I don't like users having to scroll!

 

[andymartnez]

I initially tried to make a form that functioned as a report does (before I knew about reports). The only reason I use a report is because it integrates well with queries and can display multiple records at once. I keep them in report view because I have buttons and whatnot on the report so users can access individual records from the search results. I, like you, wish that I could have the report show 5 or so records and then have a "next page" button, so they don't have to scroll through a 50 result report.

 

[andymartnez]

So another problem has arisen out of the ashes of the old one. Well, actually its been a problem the whole time but I'd forgotten about it until now.

If any of the fields for a record that are also part of my search form criteria, are left blank on the record, then it won't be returned as a search result, even if the criteria are left blank on the search form. How can I keep this from happening?

 

[CJ_London]

use the nz function there as well e.g.

(nz(T.categories) Like "*" & nz(Forms!search_frm!category_search) & "*")

will return the record if both field and form control are blank

 

[andymartnez]

I used that method with my polygon search a while back, and seeing it work so well with my keyword search, I assumed it would work, but it didn't. Here is how I implemented it. Mind you, this is not the query that we had been previously working on (I have 3 queries that are all similar, one uses the keyword search with the "or" operator, one uses the "and", and the other has neither and assumes 1 or fewer keyword fields were used.) This is the last one of those:

SELECT database_tbl.ID, database_tbl.model_name, 
database_tbl.date_created, database_tbl.thumbnail, 
database_tbl.texture_type, database_tbl.path, database_tbl.author, 
database_tbl.polygons, database_tbl.textures, 
database_tbl.licensing_rights, database_tbl.categories, 
database_tbl.subcategories, database_tbl.keywords, database_tbl.notes, 
database_tbl.associated_program, database_tbl.file_type, *

FROM database_tbl

WHERE (((Nz([database_tbl].[model_name]) Like "*" & Nz([Forms]![search_frm]![model_name_search]) & "*")) 
AND ((Nz([database_tbl].[texture_type]) Like "*" & Nz([Forms]![search_frm]![texture_type_search]) & "*")) 
AND ((Nz([database_tbl].[texture_type]) Like Nz([Forms]![search_frm]![polygons_search_low],"*"))) 
AND ((Nz([database_tbl].[categories]) Like "*" & Nz([Forms]![search_frm]![category_search]) & "*")) 
AND ((Nz([database_tbl].[subcategories]) Like "*" & Nz([Forms]![search_frm]![subcategory_search]) & "*")) 
AND ((Nz([database_tbl].[keywords]) Like "*" & Nz([Forms]![search_frm]![keyword_1_search]) & "*")) 
AND ((Nz([database_tbl].[associated_program]) Like "*" & Nz([Forms]![search_frm]![associated_program_search]) & "*")) 
AND ((Nz([database_tbl].[file_type]) Like "*" & Nz([Forms]![search_frm]![file_type_search]) & "*"))) 
OR (((Nz([database_tbl].[model_name]) Like "*" & Nz([Forms]![search_frm]![model_name_search]) & "*")) 
AND ((Nz([database_tbl].[texture_type]) Like "*" & Nz([Forms]![search_frm]![texture_type_search]) & "*")) 
AND ((database_tbl.polygons) Between [Forms]![search_frm]![polygons_search_low] And [Forms]![search_frm]![polygons_search_high]) 
AND ((Nz([database_tbl].[categories]) Like "*" & Nz([Forms]![search_frm]![category_search]) & "*")) 
AND ((Nz([database_tbl].[subcategories]) Like "*" & Nz([Forms]![search_frm]![subcategory_search]) & "*")) 
AND ((Nz([database_tbl].[keywords]) Like "*" & Nz([Forms]![search_frm]![keyword_1_search]) & "*")) 
AND ((Nz([database_tbl].[associated_program]) Like "*" & Nz([Forms]![search_frm]![associated_program_search]) & "*")) 
AND ((Nz([database_tbl].[file_type]) Like "*" & Nz([Forms]![search_frm]![file_type_search]) & "*")));

 

[CJ_London]

you are not using nz's here

AND ((database_tbl.polygons) Between [Forms]![search_frm]![polygons_search_low] And [Forms]![search_frm]![polygons_search_high])

 

[andymartnez]

you are not using nz's here

How exactly would i set it up for that line, because it's coming from two different text boxes?

 

[CJ_London]

you use nz around indivial values so

nz(a)*nz(b) is correct

nz(a*b) is incorrect

for your code

AND ([COLOR=red]nz(database_tbl.polygons)[/COLOR] Between [COLOR=red]nz([Forms]![search_frm]![polygons_search_low])[/COLOR] And [COLOR=red]nz([Forms]![search_frm]![polygons_search_high])[/COLOR])

 

[andymartnez]

I again thank you, once small step for a query, one giant leap for the database.

 

[andymartnez]

One final question, is there a way to call the "compact and repair" from VBA? (Connected to a button on a form)

 

[CJ_London]

there is in 2003, not sure about 2007.

if you put 'vba compact access database' into your search engine you will come up with a number of threads on this and other forums.

This link provides some useful discussion

http://www.access-programmers.co.uk/forums/showthread.php?t=169335