Trouble with Query Statement

I keep getting the following error with the code below:  runtime error
'13'; Type mismatch.

Set rst = CurrentDb.OpenRecordset("SELECT tblCaseInfo.CaseID,
tblCaseInfo.DHSNo, tblCaseInfo.Region, tblCaseInfo.DHSAttny,
tblCaseInfo.CaseName, " _
    & "tblAction.CaseID, tblAction.Actn, tblStatus.CaseId,
tblStatus.ClosedDate, tblStatus.StatusRptNotes " _
    & "FROM (tblCaseInfo INNER JOIN tblAction ON tblCaseInfo.CaseId =
tblAction.CaseID) " _
    & "LEFT JOIN tblStatus ON tblCaseInfo.CaseId = tblStatus.CaseId "
_
    & "WHERE ((tblStatus.ClosedDate) Is Null);")

I tested the above SQL statement as a regular query and it works.  Can
anyone tell me where I went wrong in the proper VB phrasing of the
statement above?

FYI, I am trying to pass information to an Excel spreadsheet.  If you
need more of the code to answer, let me know.
TIA
S. Jackson

0
AussiesNAgility4Me
8/9/2007 4:12:14 PM
access.formscoding 7494 articles. 0 followers. Follow

1 Replies
439 Views

Similar Articles

[PageSpeed] 10

How have you declared rst (and what version of Access are you using)?

Since you're attempting to open a DAO recordset, try:

Dim rst As DAO.Recordset

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


<AussiesNAgility4Me@gmail.com> wrote in message 
news:1186675934.064396.51850@57g2000hsv.googlegroups.com...
>I keep getting the following error with the code below:  runtime error
> '13'; Type mismatch.
>
> Set rst = CurrentDb.OpenRecordset("SELECT tblCaseInfo.CaseID,
> tblCaseInfo.DHSNo, tblCaseInfo.Region, tblCaseInfo.DHSAttny,
> tblCaseInfo.CaseName, " _
>    & "tblAction.CaseID, tblAction.Actn, tblStatus.CaseId,
> tblStatus.ClosedDate, tblStatus.StatusRptNotes " _
>    & "FROM (tblCaseInfo INNER JOIN tblAction ON tblCaseInfo.CaseId =
> tblAction.CaseID) " _
>    & "LEFT JOIN tblStatus ON tblCaseInfo.CaseId = tblStatus.CaseId "
> _
>    & "WHERE ((tblStatus.ClosedDate) Is Null);")
>
> I tested the above SQL statement as a regular query and it works.  Can
> anyone tell me where I went wrong in the proper VB phrasing of the
> statement above?
>
> FYI, I am trying to pass information to an Excel spreadsheet.  If you
> need more of the code to answer, let me know.
> TIA
> S. Jackson
> 


0
Douglas
8/9/2007 4:37:52 PM
Reply:

Similar Artilces:

Email trouble with New User in Active Directory
Below is a trail of posts that have been made. My problem has yet to resolved and I need some help quickly. The only other thing I can think of is to reinstall Exchange Server 2003. Hopefully someone will know the answer. Thanks! Subject: Re: Email not Setup when new user created in Active Directory From: "Bryan Hill" <bthill@comcast.net> Sent: 8/15/2004 12:30:21 PM We have other clients that are working fine. Just when we try to set up new ones - it will create the SMTP address but not the X400 address. I tried manually forcing the X400 but it will...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

'Replace' Query
Evening All, I have, what I hope is an aesy question to answer, regarding the 'replace' function. I have the following formula, (simplified from actual), =SUMIF($H$6:$H$84,$AA$4,I$6:I$84)+($AC$2*$AB$4/2) But I would like to add the following IF function at the start, =IF($X9=0,0, so that the whole thing should read: =IF($X9=0,0,(SUMIF($H$6:$H$84,$AA$4,I$6:I$84)+($AC$2*$AB$4/2)) Firstly, I have tried replacing =SUMIF( with =IF($X9=0,0,SUMIF( but it does not allow this as there is no second closing parenthesis. Also, if this is possble, I would also like it to be able to automatical...

Trouble with the template.
I want to reset the 'normal' template to default to "Times New Roman" 10point font, and the pages numbered centre top. This seems to work when I set and reopen Word2003 from the Administrator Template vis: C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates. But it will not work when I open Word2003 normally from the desktop. I have deleted the "Normal.dot" from the following and copied in the "Normal.dot" from the Administrator above: C:\Documents and Settings\All Users\Application Data\Microsoft\Templates...

Need to get result of a select query in code
I have a select statement that I need to run in code. The select statement needs to reference 2 variables which are in the code. I know that the usual way to run a select in code is with DLookup. This if fine because I only need 1 column of 1 record even though the select can return more than 1 record. But, the query needs to reference those 2 variables in the code. How can I run a select in code which references 2 fields in the code and get the result of 1 record (only)? If necessary I could put those 2 values in textboxes on forms as I have several queries which reference textbox...

In query how to write group into the same minute? Export to text file?
Hi How can I in a Query write the criteria so that it groups the data from witin every minute? For example many trades that are made in the same minute compressed into that minute (and the analysis will be done in other fields, like: first, high, low, last, sum). And how can I export the result from a query into a testfile? Regards Rolf On Fri, 5 Feb 2010 00:05:00 +0100, "Rolf" <rolfe@algonet.se> wrote: >Hi >How can I in a Query write the criteria so that it groups the data from >witin every minute? For example many trades that are made in the ...

trouble with CrmIsapi.dll
I was experiencing the same problem with the SDK as many have posted to this group ... "the WhoAmI() call results in a http error 405 when running sample code" I followed some of the advice; checked my .srf mapping to CrmIsapi.dll and re-registered the CrmIsapi.dll. when I re-register CrmIsapi.dll I get an error 80004005 - unspecified. ??? I copied it to various places and retried it ... same result. I got the original from the disk and retried ... same result. unmapped it from .srf, restarted and retried to register it ... same result. Anyone experienced this. What's up with m...

Trouble with Outlook 2003 and PST File
Hi, We have a user who is using Outlook 2003 and moves most emails to a PST file which is considered archived email data. They have reached the Max limited on this PST file of 1.99GBs and its not letting them open the PST file. It is trying to repair it when they try to open the PST file. Here is the message they receive: "Can't move the items. The file <path>\<filename>.pst has reached its maximum size. To reduce the amount of data in this file, select some items that you no longer need, and then permanently delete them." I can't even get it open to delete...

unexpected conversion trouble
I wanted to open an Excel file I hadn't been working on for a while, an Excel couldn't do it. After some investigations, I found that even if the file still had th .xls extension, it seemed to be conveted in .doc format (in fact, whe I open it in notepad, I can see at the end some words that look like MSWord signature). I don't know how this happened, but I'm reall embarassed beacause i really need the data in this file. I tried to change the extension to .doc, open the file in Word and the import in Excel, but I can hardly see part of the file. What's more, i is a 3-she...

I am in trouble
Hi, I am a student in Cambodia.I don't have any girlfriend. I tried everything. I looked at them wrote poem to them it doesnt work. I got an account from facebook, hi5 friendster myspace. I have 0 girl friend. What should I do? I am so desperate. I am good looking and 250 kg only. Please help me or at least send me some foood I am hungry Best Regards Hungry Guy __________ Information from ESET NOD32 Antivirus, version of virus signature database 4944 (20100314) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com You're tr...

Trouble with Pivot Tables & Named Ranges
Can I use a dynamic named range setup in one file as a range in pivot tables in another file? When I have the data source file (with the named range) and the pivot report files open, the pivot reports update just fine. But the moment I close the source data, the pivot reports no longer can update and I get a message that the source can't be found. If I reopen the source, the pivot tables work fine again - but as soon as I close it up, the reports no longer work. The source file is very large and I don't want to open it every time I have to update all the reports tha...

Repost-Edit and save query criteria
Sorry to repost this, but I was not sure I would get help with this again and it would get lost in the list. As suggested, I created a table called ExcludeShows1 with the ShowID and Year fields. I have posted the SQL with those changes to the query. When I tested this initially, it appeared to work. Then I tried testing the queries using last year's show data for 2009. No data is being returned for 2009. Not sure what is happening that it is not returning 2009 data. SELECT Shows.ShowID, Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([...

Query
I have changed my default mail program from Microsoft Outlook to Outlook Express but emails are still going into Microsoft. How do I make them go into Outlook Express? Any help much appreciated.... On Aug 19, 12:35=A0pm, "Ron Head" <ronh...@iinet.net.au> wrote: > I have changed my default mail program from Microsoft Outlook to Outlook > Express but emails are still going into Microsoft. =A0How do I make them = go > into Outlook Express? =A0Any help much appreciated.... You would have to set the account up in Outlook Express as well. Changing the default email ...

Trouble with "time" in formulas
I have a time sheet that looks something like this: A1=time...A2=time...A3=A2-A1+if(A1/24,1)...A4=if(or(A3<time(0,15,0),A3>time(4,0,0)),A3,time(4,0,0)) A1 and A2 are set up as dropdown lists and the times in the list are i increments of 15 minutes. WHat I'm trying to accomplish is (in cell A4 if A3 is between 15 minutes and 4 hours, then return 4 hours. if A3 i 0 or greater than 4 hours, return A3. If I enter enter times in A1 around 00:00 and A2 a little after 00:0 it works fine... example: A1=22:00...A2=01:00...A3=03:00...A4=04:00 A1=20:30...A2=00:15...A3=03:45...A4=04:00 A1=00...

trouble
I have been using my MS Publisher 2000 for over 3 years. Just recently I attempted to access the Publisher program and got the following message: "This application must be installed to run. Please run setup fom the location where you originally installed the application." I have done everything I know to fix this including uninstalling and reinstalling the program. I don't understand what happened overnight nor do I understand the instruction "run from the location you originally installed the program". I can not access the numerous publicaions that I have cr...

Trouble sorting a table???
Using Excel 2002) I am relatively new to Excel and can only really perform simple functions in a basic spreadsheet. The spreadsheet I'm having trouble with is pretty basic, but I'm having trouble sorting some of the results from a table. (I'll try and explain as best as I can) I have 10 people that I'm tracking 5 different things for (dates, debts, credits, items, number of transactions, and their balance as calculated in this table by simple SUM functions) I then have another table with these same 10 people with their total balance. This is the table I'm having t...

MS paramater Query on ODBC Table
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to and if so how can I run a parameter driven MS query on = an ODBC database table? I want to be able to enter some info into a cell on a spreadsheet and = have a query run to pull back other info relating to my entry. --=20 Regards Dean=20 dkso@ntlworld.com=20 http://homepage.ntlworld.com/dkso ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/html; charset="iso-885...

combobox uses query
Hi, On a form i have - a combobox called "combo_house" based on a query called " Houses". In the query i have a column called "price" and a column called "transdate" which is a date. On the form i also have 2 toggle boxes; one called "sold" one called "not sold" I would like to be able to filter the query used by the combobox when the togglebox is checked. So when i check "sold" the query used by the combobox should be filtered on the value in column "price" > 0 OR column "transdate&quo...

Having trouble
I have a set of numbers, a-e. A usually equals e but can be less. B remains constant. C is the sum of A and B. C cannot exceed D. D remains constant I have tried the following formula in the box for A, but C ends up being less than D when I want it to equal D =IF(C>D,E-(C-D)) What am I doing wrong? Uhh, I'm a little slow, but; If you are putting this formula in A and it's the same "A" that is part of the "C=Sum A & B" then I think you have a circular reference. In other words, you're asking xl to resolve "A" by asking "If C> D&quo...

Print Preview troubles
When I do print preview, the information in the scratch area gets overlaid on the document, and there are text boxes where there are none in the work area. In the work area mode, the docs look correct and the stuff I have in the scratch area is OK. It is a scrambled mess. I have used "bring to front" and its' mates-no help. Using Publisher 2002 to create a 8 page booklet on 11x17 stock. I may add: this document had been working but something happened to it, most likely my fault, but I do not know what. Thanks, Jim This was a bug in 2002. Drag the objects to the top or bottom in ...

List Box in queries??
It there anyway to have a list box in a query? For Ex. in the Criteria box in order for a msg box to display you have to enter [] then you can "type" in what you want it to search for. My question is can I have a drop down box in that Messagebox? On Tue, 19 Feb 2008 11:13:02 -0800, Tiff wrote: > It there anyway to have a list box in a query? For Ex. in the Criteria box in > order for a msg box to display you have to enter [] then you can "type" in > what you want it to search for. My question is can I have a drop down box in > that Messagebox? No you ...

Creating a customer accounts statement vis query?
Hi, i use access to create invoices for jobs that i do.. currently i have the following...Jobs database that includes a the cost for the job. I print an invoice from this information... The layout is basically the following..job.customerIDjob.descriptionjob.datejob.chargeI also record payments in a separate database called Payments because the payments are sometimes different than the charges...payment.customerIDpayment.datepayment.amountI would love to create a report that uses the data from both of these tables and generates a statement like so...Date Credit Debit1/2/07 �5...

Query methods
The topic doesn't refer to typical Access "methods", say as a method attached to or associated with a control. I'm referring to ways of building a select query based on control property values and how I might arrange them in a query. I have some controls on a form that become enabled and disabled depending on whther or not those controls have one or more date values. For fields that have one value, the associated control presents that value in a plain text box. For fields with mulitple values, the associated control presents those values in a combo box. The controls overl...

Trouble with a domain with a
I'm trying to send email to the domain PCM-LWV.COM and I keep getting this NDR: The message could not be delivered because the recipient's destination email system is unknown or invalid. Please check the address and try again, or contact your system administrator to verify connectivity to the email system of the recipient. < ylpvm25.prodigy.net #5.1.2> Everything seems fine with our exchange 2003 server otherwise. We are receiving email from this domain, and when we reply we get this NDR. We can send email to any other domain.. is it the - in the domain name?? I tes...

Strange Query by Form Problem
Hello all, I am having something weird happen to me when I try to run a query/report from a form. The form has: Combo box - cboPickTask Text Box - txtQCriteria CmdButton - cbutRunQuery cmdButton - cbutGetRep It is an unbound form and is simply used as the selector for the query criteria. User selects task from dropdown menu, query returns all personnel who have not completed the task. Personnel are either supervisory or non-supervisory. In the combo RowSource is a column beside the task name that is either "All", "Supervisory" or "Non- Supervisory". I am tr...