Unable to insert Access queries
When trying to use ms-query to import data from an Access database, I've found that it doesn't allow me to open some of the queries (views) from access. It seems that the queries I have trouble importing have the nz() function in calculated fields of the access queries (views) (or in a sub-query). Is this simply not supported or is there a way to work around it?
In the Access query, instead of Nz, use an IIF formula. For example:
Craig A wrote:
> When trying to use ms-query to import data from an Access database, I've found that...Question on Query 06-06-07
I have created a query that searches a database based on a zip code entered
by the user.
The question is: The database contains both 3 digit(first 3 digits) zip
codes and 5 digit zip codes. I want the user to enter all 5 digits and still
be able to pull up the 3 digit codes that match. How do I configure the
query to do this?
Are you saying that the 3 digit and 5 digit zip codes will be in the same
field in the table?
WHERE [ZipCode] = [What Zipcode?] OR [ZipCode] = Left([What Zipcode?], 3)
If not, and they're in separate fields, you'll want
WHERE [ZipCode] = [Wha...Duplicate date prompts when using Macro to run Crosstab Query
I am having an issue when I run a Crosstab Query via a Macro. I get prompted
for duplicate Start and End Date. This only happens when I run the Query via
When I run the Query without using the Macro it works fine and only prompts
me once for Dates.
Does anyone know what I am doing wrong?
Message posted via http://www.accessmonster.com
I can't see a valid reason why the parameters should pop up twice only when
the query is executed by macro. (What version of Access is this?)
A workaround might be to create a little unbound form with a couple...How to add multiples records from a querie
I have a unmacth querie that inserts me into a table the records that are
My problem is that when I have one one unmatched record i need to insert
that record 12 times. I have to insert 12 time each record that I have
What happens is that anytime I insert a record I have a repeat it 12 times
so I can have:
All the fields are equal but the this filed must be diferent.
I can I insert 12 records automatically?
Try creating a table named Period with a field named period containing
numbers 1 (one) through ...access query 05-24-07
I have a query in my database which includes the following fields from
However I cant figure out how to display only the results where 'stockLevel'
is less than 'reorderLevel'.
...images in select query
I have images in a folder and path is saved in a table called art_master
i want to insert images in a field in select query
im using sql 2008
On Tue, 4 May 2010 15:19:51 +0530, "Capri" <NoEmail@NoDomain.com>
>I have images in a folder and path is saved in a table called art_master
>i want to insert images in a field in select query
>im using sql 2008
This is not possible in a SELECT query, you may want to look
converting the files to be stored in the database using the filestream
dataty...using a date variable in query criteria
I want to use date variables in the criteria of a query.
When I put Between #1-1-07# and #1-30-07# it works
but if I try to use a date variable it doesn't work. In
the query by example view. The field in the database
is a string but I convert to a date using
Datevalue([work_order_received]). I want to calculate
a date like this in the Field:
dateback: datefrom: ( year(Date())-1 , 1 ,1)
When I try to use the variables (datefrom dateto) in the
criteria statement this doesn't work
...query run in subform and not in separate window
i've created my form and have a query that runs within a subform.
This is working fine except that the same query runs in a separate
How do I prevent the separate window from opening but only run within
What do you mean by having "a query that runs within a subform"?
What are you doing to run it?
What kind of query is it? If it's a SELECT query, typically you'd set it as
the RecordSource for the form being used as the subform, and not run it. If
it's an Action query (INSERT INTO, DELETE, UPDATE, SELECT ... INTO), you ...Query on zip codes
I have a query that searches a database for matching zip codes. The user
enters the desired 5 digit zip code and the query displays the necessary
The database has zip codes stored as 3 digit (first 3 digits of zip) and 5
How can I write the query to find both the 3 and 5 digit matches?
"Dirk_Bob" <DirkBob@discussions.microsoft.com> wrote in message
> I have a query that searches a database for matching zip codes. The
> enters the desired 5 digit zip code and the query disp...Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table
using MS Query. When I import the workbook my options are only to select the
"tables" (which are my sheets referenced as sheetname$). I don't want to use
the entire sheet, just my named ranges in multiple sheets.
Names ranges should appear in the list of tables, unless they're dynamic
ranges. But if there's nothing else on the sheet, you can use the
> I want to use multiple ranges (named) as the data source for a pivot table
> usi...Emailing Query Results in Excel
Hi. I am using Microsoft Query in Excel, and the results
come back fine; however, when I go to Send To--Mail
recipient, then choose "sheet", it sends my spreadsheet
without my Excel Query data. How can I email the results
of my query to another person? I have tried this several
times, and every time you see the outline of the
spreadsheet, but no data.
Try copying the data and then Paste Special/Values to another location.
"CJ" <firstname.lastname@example.org> wrote in message
news:1936c01c41c46$b5aa93c0...report on a cross tab query
More options Feb 7, 2:38 pm
From: subs <subbu1...@gmail.com>
Date: Sun, 7 Feb 2010 11:38:53 -0800 (PST)
Local: Sun, Feb 7 2010 2:38 pm
Subject: access report on a cross tab query
Reply | Reply to author | Forward | Print | Individual message | Show
original | Remove | Report this message | Find messages by this author
i have a cross tab query
which gives an output like below with following columns-------
ozip dzip week no1 week no2 week no3
columns week no1 , week no2 and so on could be dynamic-- it ...Turning off action query confirmation messages
How do I turn off ALL confirmation messages for make-table and append queries
when running them from a macro in an Access 2007 .accdb database? I've tried
following a 2004 posting which says:
"If you're using a macro to run the query, put SetWarnings
False before and SetWarnings True after running it.
Doug Steele, Microsoft Access MVP "
.... but they aren't accepted as a valid commands in the macro setup. I don't
know SQL; I'm using plain vanilla Make-Table and Append queries called out in
OpenQuery commands in a macro. The confirmation message...Linking Same Tables (Current/Previous) in a Query / Sum is multiplying value results
I'm trying to link two tables in Access...both tables contain the samefields with YTD totals. I need to subtract YTD Total between the two,to determine the difference. I have the tables linked via 2 fields.I group by the two fields, and sum on YTD. It is multiplying thevalues and I'm ending up with numbers in the 200,000 instead of max ofabout 5,000.I've tried doing queries separately, doing the sum, and then linkingthe two new queries...only to have the same results.I've tried turning them into Make-Table Queries, creating the tablesand running the new query off of the 2 ta...Query based distribution list problem
Running exchange 2003. Our query based lists work fine except the sender
gets an error message for accounts that have expired. Is there a way to get
Did you mean accounts that are disabled?
Accounts with expired passwords shouldn't have an issue receiving mail,
"Fuzzy Logic" <email@example.comREMOVETHIS> wrote in message
> Running exchange 2003. Our query based lists work fine ...Displaying Query Results in a Textbox
I am having a problem getting the results of a query to appear in a
Does anyone know how to do this?
Please give a few details.
<firstname.lastname@example.org> wrote in message
> I am having a problem getting the results of a query to appear in a
> Does anyone know how to do this?
...Query plan isn't doing an index seek
If you have the following:
SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID =
FROM Work ws
What I get is a Hash Match from 2 index scans.
|--Hash Match(Right Outer Join,
|--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR]))
|--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS ...Aggregate query and Albert Kallal's Multiselect example
I am attempting to build a continuous form that will allow users to
'purchase' items. The recordsource is an aggregate query that groups by part
number and a boolean 'Rush' field, and sums the quantity ordered. The query
works the way I want it too...
Now I am trying to have a checkbox on this form for users to select, and that
would mark the item(s) for ordering. I added in the 'ID' field that is the
PK for the 'parts' - and used this while following Albert's Multi-Select
example. The problem is that there can be 2 of the same items to be orde...One result from multiple memo fields 04-25-07
If I have duplicated this post I am sorry,
my computer froze, so I wasn't sure if the first post got thru.
I am an access newbie so I am not even sure this can be done...
I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...
My report has the following record source:
SELECT tblJob.Job, tblNotes.Notes FROM tblJob LEFT JOIN tblNotes ON
The report has a Group header on Job
and a [Material] text field with the following control source:
=IIf([Notes] Like "Material*",[Notes],"No Material listin...Name of the sheet where the formula result is
I use max()and min() in the result sheet to define the max
and min values in 10 sheets where test data are. How can I
display the sheet name of the result from the
calculations? For example, if the result of max() is from
Thanks a lot
You will probably need a macro to search the sheets for the values. I
would probably be a good idea to keep searching to the end of the las
sheet to check for duplicates
Message posted from http://www.ExcelForum.com
This might not work for you, but I'd lay out part of my summary sheet like:
Sheetname Max (this formula...Problem with parameter query using between ...
I am trying to build a parameter query for which I would enter two values and have all the values in between returned. For example, a list of numbers from 1 to 200.
On the query I have the following on the Criteria:
Between [Enter Product min number] And [Enter product max number] & "*"
So when I run the query first window pops up, I enter 100, then second one pops up, I enter 150 and after that I get a message saying that the expression is typed incorrectly or it is to complex.
Also, I am trying to get a query of the same kind where I can type on the parameter something like ...Very difficult query (?)
I've been struggling with this all day, and although I think I'm near a
solution (actually I thought that 2 hours ago!) it now involves 4 nested
queries and is 'grinding' horribly (i.e. takes 30-40 seconds to run), and
I'm convinced that there must be a better way to get what I need! I really
hope someone can help with this.
I have a table [x confirmed] which includes the following fields:
'month number' (integer)
'cont rate' (number - percent)
'nhsp ees' (number - currency)
'pen pay' (numb...Unable to bring back all results from table with query
For a uni assignment we have a designated query that we have to create.
But when I run the query, I get one result returned to me. I should have two
returned to me, as I have two records in the Problem table.
The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User
Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code
No], Problem.[Problem Description], Problem.[Current Activity Log],
FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] =
Problem.[Asset Code No]) INNER JOIN [Contact Log]...Error using Data Query within Excel 2000
I receive the error Invalid bracketing of name 'xls"
not enough infomation. post example of "invalid Bracketing"
also data query code.
> I receive the error Invalid bracketing of name 'xls"
I'll do my best I can to explain.
I have a report that is based on criteria entered from a form. On the form
the user has to input 2 sets of dates. The current month and the previous
month(which would be the previous year). So if they put in 07/01/07-07/31/07
for the current year and 07/01/06-07/31/06 for the previous year they would
expect shipping totals for each.
The report does not have a record source. But it does have 3 subreports
within it. Sales Rep Totals, Market Categories & Parts/Service are the 3
How can I get my data to show up correctly if my ma...