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:
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?
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 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
I have created a module in VBA that creates an Excel spreadsheet when executed (mdlExcelCreation).
I have also created an SQL query, that selects records from a view (qrySpreadsheetData).
I am at the stage now where I wish to combine the two, so that a spreadsheet gets created and the records rectrieved from the SQL query are put in to the spreadsheet.
Is this possible at all?
I am thinking about creating a new module that when called, executes the module (mdlExcelCreation), and then executes the SQL query, but I am not sure if this will work.
Could anybody advise on this please on the best way to accomplish this.