Create a variable from a query created in VBA
What I'd essentially like to be able to do is create a query:
docmd.runsql("SELECT x from y where z;") [note: actual query will be a lot more complex than that and include subqueries]
.and use the result of that as a variable. I don't want to create permanent queries because I'd like to avoid the clutter that would come from that.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
Unfortunately, the variable does not seen to be passed on to the query, although the query function call is triggering the VBA - it just doesn't seem to be returning the value into the query.
I have tested the rest of the code and the value I am trying to get into the query criteria is working up to the point of the query calling the function,and then the criteria doesn't work in the query.
I have another function in the same query, set up the same way, and that one works fine for an integer value that I am using to return a unique event ID.
I have recently discovered that the variable being stated as Date has an output of time.
In the vba function I define both the function (calcdate(showthisdate)) and variable (showthisdate) as Date
the variable then as a date stored into it using a combination of the DateValue function.
I then placed calcdate(showthisdate) as a query critria. After this not working and serveral changes did nothing I placed the function as a new field to see if the variable was being passed. This field defined as Date is showing 0:00:00 in all the rows.
Looking into it it I found that defining something as Date also adds a time format aswell
Question how can I define the function and variable just as date instead of date and time
I have a field in this same table that shows a department value for each record. That fieldname is “Dept”.
Within a huge VBA function module I’m writing, I have a string variable assigned to the Query field called strQuery. I also have string variable assigned to the Dept field called strDept.
The query that is assigned to the variable strQuery is designed to return ALL results for ALL departments. However, I want the VBA to run the query and return results for only the department that are assigned to the variable strDept.
If I leave out the department limitation, it works fine with the following code:
So I want to store some variable at some place in the vba or access that I can afterwards collect that variable again when I rerun my code.
Only way I know is to add it into a table, but is there some other, easier way?
I have a combo box and based on the user's selection(s), a 'where clause' is created. I need to either create a query that includes my where clause, or use an existing query and use the where clause.
I had created a query in VBA in Access 2003 using DAO defs, e.g.,
set qdef = MyDB.CreateQueryDef("q_regions", strSQL)
This does not work in 2007.
I have tried setting the criteria field to the current record of the form, setting a variable to the ID field. But the query is not getting the information to compare and the output of thereport is #Type and #Error.
When the macro is run, the query runs and prompts for the information the criteria is looking for.
How do I pass a variable from a form to a query in VBA?
I think storing a value to a public variable is fairly easy, but Im not sure how, in a query, to call that variable as a criteria
I thought the best way to do this is to create a query with the basic fields needed and then add the "variable" fields by allowing the user to select them using a list box.
How do I transfer the listbox selection to the SQL statement
Returns goes back to the original code.
But when I check the value of the variable in the originals form code with a msgbox. it is blank.
When the code finishes running (there is just one or two lines of code which do not affect the variable). I debug.print strinput and it has a value.
Why does debug.print recognize the value of the variable but the code within the form (it is an afterclick event subroutine) not recognize it? The public variable is declared in the original form.