Create a variable from a query created in VBA

I would like to create a dynamic query using VBA and use the query's result as a variable. I have done this before, without using dlookup (I think it's too complex for dlookup), but I cannot seem to remember how.

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.

Sponsored Links:

Related Topics

Date variable is being displayed as an empty time variable
Looking through this forum you can see that I have been trying to work out a vba function and query problem.

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


Problem with query accessing VBA function
I have written some MS ACCESS 2003 VBA that allows a variable to be passed to a query, using a form to harvest criteria for the query.

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.


Filting query results within VBA code module
I use Access 2010 but haven’t written much VBA code and could use some help. I have a table that provides the name of a query in a text field. The field name is “Query”.

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:


Embedded/nested? Variable
Is there some sort of internal variable or table that can be used to store temporary variables? While running code you can obviously store data in variable, the variable are dumped.

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?


Create a query in VBA, Access 2007
I need to create a query in VBA. I'm using Access 2007.

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.


Lose Variable
I have my users login so I can set UserPolicy. When they login I store their User Name in a Public Variable called strUserLogin. However, the weird issue is it seems to lose the variable after a while. Because I use the variable also to set who created an appointment or modified it last. Unfortunately, a lot of them are coming up blank. I checked the code, the code is correct - I also set the date modified and date created and they are always getting filled therefore the only thing that I can think of is it is losing the variable. Any idea why it will lose the public variable and how I can prevent it from happening


Variable Not passing to query
I have a command button which when clicked, runs a VBA macro which saves a report to a PDF for the current record. The report is based on a predefined filter for based on the current ID field on the original form.

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?


How to call a Public variable in a query?
I want to be able to, when a certain button is pushed, pass a variable to a query. These buttons would be utilized in several forms as subform so I can just code the event procedures once.

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


VBA doesn't recognzie variable's contents
I have declared a public variable in my form's vba code

[code]

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.


Build Query in VBA using List Box to Select Fields
The user would like to be able to create queries dynamically. ie. She would like to be able to select which fields are in the query. Each of the "variable" fields to be selected are Yes/No Checkboxes.

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


SQL Limit in VBA?
Is there a limit to the amount of characters I can use in an SQL statement using Access VBA?

I ask because I've loaded a variable with a query (which I got from Access) but now it's telling me "A RunSQL action requires an argument consisting of an SQL statement."

I pumped out the variable a Message Box, with some investigation I noticed that the Message Box cuts off after 1,024 characters (I assume that's a limit given the number) and wondered if it was possible that Do.Cmd.RunSQL has a limit of as well?

[CODE]

I also pumped out the variable to a text file as well, this is the SQL that's been sent to the variable,


"create table" issue
First post on the forum! On to the question. I am trying to get a table created in VBA that gets its table name from a form entry thats stored in another table, passed into a variable, and used as my table name. im getting a syntax error message box when running this section and cant figure out what ive done wrong.

Relevant code follows but first some things ive already tried. saved the whole thing as a string variable and used docmd.runsql. ive used quotes around field name and datatype. im stuck on this one


Referencing and Changing A Form Module Variable From A Module
I would like to reference and subsequently change the value of a variable I have in a form module from another module.

So, in the form named frm_order, the variable is in the form's module and is named frm_var_1. This variable is declared as a public variable.

I then created a module via the module tab called mod_check, in which I want to reference and change the variable frm_var_1.

Also, what is the appropriate way to refer to these types of modules. I would imagine anything created in a form is a 'Form' module. What about modules created using the modules tab? What is the technical name for them?


Referencing and Changing A Form Module Variable From A Module
I would like to reference and subsequently change the value of a variable I have in a form module from another module. So, in the form named frm_order, the variable is in the form's module and is named frm_var_1. This variable is declared as a public variable.

I then created a module via the module tab called mod_check, in which I want to reference and change the variable frm_var_1.

Is this possible? Also, what is the appropriate way to refer to these types of modules. I would imagine anything created in a form is a 'Form' module. What about modules created using the modules tab? What is the technical name for them?


Call VBA module to create spreadsheet along with SQL query to populate spreadsheet
My name is Dan.

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.


How to support nullable integer (number) field in Access / VBA
I have run into a dilemma that integer (number) variables in VBA do not seem to support null, but as for a column type in a table, that is allowed to support null.

I recall while developing with the DB2 database, that the DB2 driver / API supported the concept of fields having null indicator variables. That is a second variable that may be checked to find out the null state of the field in question. The correct way to deal with nullable fields on DB2 was to use that second variable, do the null check BEFORE attempting to access the variable which is suppose to contain the value of said field.

Does Access / VBA have anything comparable in ADODB.Recordset objects?

As-is, Access / VBA turns the NULL that was in that field of the record into 0 upon issuing a SQL UPDATE as the Integer variable defaults to 0.


Passing a variable to a query
I am developing an access solution, not having used VBA for 7 years. Thrown in at the deep end some might say.

My problem is thus;

I am going to need to generate reports, based upon user input (OrderID). However, this order ID is not provided by a user, in the form of a text box but is selected from a multicolumn listbox. I know how to generate a query based upon the contents of a form field, textbox etc but is it possible to reference a variable stored within an open form from a query


Use query value in VBA
In my logic, I am trying to use value from a query and assign it to a variable. The query just has 1 record and has basically count of records. I want to use this count for some calculation such as

cnt = 1 + (count from the query)

I tried to use the logic below but it does not work. Should I create a table fromt this query and then use the value?

Cnt = 1 + DoCmd.OpenQuery(Qry_SiteCount)


A Reminder Re: Module-Level Variables
I just spent an hour troubleshooting an error, until I finally found something I hadn't expected: a local variable was masking a module-level variable. I thought I'd share this reminder: if you have a module variable named X, and you declare a local variable named X, any actions in that method will use the local variable. Unlike areal IDE, the VBA tool will give you no warning.


queryDef Parameters and recordset, storing to variable
I am trying to read from a stored query in MS Access to a variable. My error: Run-time error '91': Object variable or With block variable not set.