Search results

Thanks for the help Micron. I originally thought I would create a static query, which is why the record source was set to that, but then I realized it would have to be a dynamic query. I didn't bother to change the record source because I figured that I was setting it in the code. Appreciate...

sqlSearch = sqlSelect & sqlFrom & sqlWhere & sqlSort & ";" sqlsearch is the variable that is defined in the above code. I thought I should be able to set a sql query as defined in the code as the record source, am I wrong? There is no separate query called sqlsearch As previously posted...

See attached. The main page opens, click on search, If you enter a year of 2020 and a description of unitec it should return a record, however it errors out. I had to recombine a split database and cannibalize the whole thing to get it to fit. With some of the attempts to set the...

Correct, there is a search button on the main form. The line it breaks at is: Forms![SearchForm]![SearchQuerySubform1].Form.RecordSource = sqlSearch When I use that code I get a different error. Run Time Error 2467, the expression you entered refers to an object that is closed or doesn't...

The code breaks on any of the attempts to set the record source. Runs fine until then. The name of the main form is SearchForm The subform is SearchQuerySubform I call it a frame, but I guess it might be the control, but the holder of the subform on the mainform is SearchQuerySubform1 and its...

Thanks, the source object seems to set correctly, its when I try to set the record source that I have the error. I tried Forms![searchform]![SearchQuerySubform1].SearchQuerySubform.Form.RecordSource = sqlSearch Forms![searchform]![SearchQuerySubform1].Form.RecordSource = sqlSearch and those...

I am trying to create a dynamic sql query. I have created a search form with many different text boxes that a user can choose to search by, and when they click search a subform comes up with the results. However when I try to set the record source of the subform I get an error 438, Object...

Arnel's second solution is dynamic. It uses the variable I created to append a line to the original query in vba without having to hard code the original query in vba, which I was trying to avoid. I liked your solution with adding and then taking away the sort order, but I thought his was a...

Sorry for the delay in responding, I got pulled off of this to work on something else for a few days. Wow, everyone here is amazing. I can't thank you all enough for all the help. I appreciate the time and effort that you all took. I wound up going with arnelgp's solution. It was the...

Here is a sample with fake data, all entries are for date 7/8/18. If you remove the in statement from the query everything works great, its just all in the wrong order. BTW I tried it with both '' and [] and didn't seem to make any difference. Appreciate everyone's help on this. Can't...

Using the examples, I noticed that they added a () as part of the pivot statement. I added that and it no longer gives me that error. Now however when I run the query it is acting like the function is blank, even though when I check the variable it is showing the correct value. I will try to...

Thanks for this MajP. I think I am a little closer? But I still can't get there. If I just manually enter the headings then I lose the dynamic aspect of this. I want to allow the user to add/change types as needed, and they are not IT people, and they wont have anyone to support this and...

Can you offer some examples? Not really sure what you mean. I am trying to sort the order of how the columns come up in the crosstab query report Now it reads: WE Date, Item1, Item2, Item3....etc I want it to be: WEDate, Item3, Item1, Item5...etc Which would be based on the sort order field...

I currently have a database that generates a dynamic report based on the dates they select as well as the class_id. To generate the report I use a crosstab query. The crosstab query is based on a query with the following fields: CCUST_ID, WEDate, Item_Type, Quantity, DESCRIPTION, Class_Type...

That works great. I think I was trying to over complicate it. Much appreciated.

I currently have a sort module that I use in multiple forms and subforms to sort the records when the end user clicks on the field title. One click for ascending and two clicks for descending '------------------------------------------------------------ ' Sort_CPRTAGSASC '...

Most of the edits shouldn't be done on the search screen, there is mainly one thing we edit, but I can add it to another area and would be ok without editing the query. This database was built based off another database designed by someone even worse then me at this and so a lot of the...

When I try to change it to a right join I get "Join Operation not supported" error. FROM (Requests INNER JOIN Items ON Requests.[CGER Ticket] = Items.[CGER Ticket]) RIGHT JOIN CheckTagged ON Items.ID = CheckTagged.ID hopefully this link will work for the relationships...

Ok, I have figured out how to do question 1. I created a new query which has two fields item, number and countOfCPR(decided a count of how many tags would be better then a yes or no because sometimes things get backordered) and altered my query to reflect this. New query below. However this...

Thanks for the quick reply Minty. Sorry I wasn't more clear. For question 1 I meant how would I go about calculating that in my query that won't slow it down. For question two, do you mean like creating a field in the main query that would combine all the cpr's similar to how it was...