Error 3464 Data type mismatch in criteria expression for between 2 dates look up

I am receiving a Runtime Error 3464 Data type mismatch in criteria
expression. This form worked prior and now does not work. Below is the
code. During Debugging the (DoCmd.OpenReport strReport,
acViewPreview, , strWhere) is highlighted. Please Help.



Private Sub btn_UpdatedReportBuild_Click()
    Dim strReport As String 'Name of report to Open.
    Dim strField As String  'Name of date field.
    Dim strWhere As String  'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"

    strReport = "rptCourses"
    strField = "tblcourse.DateChanged"

    If IsNull(Me.txtupdatedfrom) Then
        If Not IsNull(Me.txtupdatedto) Then  'End date, but no start
date.
            strWhere = strField & " <= " & Format(Me.txtupdatedto,
conDateFormat)
        End If
    Else
        If IsNull(Me.txtupdatedto) Then   'Start date, but no end.
            strWhere = strField & " >= " & Format(Me.txtupdatedfrom,
conDateFormat)
        Else                               'Both start and end dates.
            strWhere = strField & " Between " &
Format(Me.txtupdatedfrom, conDateFormat) & " And " &
Format(Me.txtupdatedto, conDateFormat)
        End If
    End If

    Debug.Print strWhere
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

0
shn
2/19/2007 9:00:51 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1931 Views

Similar Articles

[PageSpeed] 15

Nothing jumps out at me.

Does it fail in all cases, or only one specific combination of 
txtupdatedfrom  and txtupdatedto being Null or not Null? What's in strWhere 
when it fails?

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


"shn" <shondra.allen@horizononline.com> wrote in message 
news:1171918851.225903.108260@p10g2000cwp.googlegroups.com...
>I am receiving a Runtime Error 3464 Data type mismatch in criteria
> expression. This form worked prior and now does not work. Below is the
> code. During Debugging the (DoCmd.OpenReport strReport,
> acViewPreview, , strWhere) is highlighted. Please Help.
>
>
>
> Private Sub btn_UpdatedReportBuild_Click()
>    Dim strReport As String 'Name of report to Open.
>    Dim strField As String  'Name of date field.
>    Dim strWhere As String  'Where condition for OpenReport.
>    Const conDateFormat = "\#mm\/dd\/yyyy\#"
>
>    strReport = "rptCourses"
>    strField = "tblcourse.DateChanged"
>
>    If IsNull(Me.txtupdatedfrom) Then
>        If Not IsNull(Me.txtupdatedto) Then  'End date, but no start
> date.
>            strWhere = strField & " <= " & Format(Me.txtupdatedto,
> conDateFormat)
>        End If
>    Else
>        If IsNull(Me.txtupdatedto) Then   'Start date, but no end.
>            strWhere = strField & " >= " & Format(Me.txtupdatedfrom,
> conDateFormat)
>        Else                               'Both start and end dates.
>            strWhere = strField & " Between " &
> Format(Me.txtupdatedfrom, conDateFormat) & " And " &
> Format(Me.txtupdatedto, conDateFormat)
>        End If
>    End If
>
>    Debug.Print strWhere
>    DoCmd.OpenReport strReport, acViewPreview, , strWhere
> 


0
Douglas
2/19/2007 9:43:05 PM
It fails in all cases.

tblcourse.DateChanged Between #11/01/2006# And #02/19/2007#

Is in the strWhere when it fails.


0
shn
2/19/2007 9:56:18 PM
When using the OpenReport WHERE argument, omit the table name qualifier. 
Access already knows what recordset the report is based on. Trying to tell 
it a 2nd time cause confusion (i.e., an error).

Access only expects the name of a field in the report's recordset:

DateChanged Between #11/01/2006# And #02/19/2007#


HTH,


"shn" <shondra.allen@horizononline.com> wrote in message 
news:1171922178.814987.136590@h3g2000cwc.googlegroups.com...
> It fails in all cases.
>
> tblcourse.DateChanged Between #11/01/2006# And #02/19/2007#
>
> Is in the strWhere when it fails.
>
> 


0
George
2/19/2007 11:26:47 PM
Reply:

Similar Artilces:

Excel Progrom Error
Hi there Happy new year to all. I often receive the folowing error message in a box titled "Program Error". EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. The only option is OK which closes Excel. I do not know where the error log is created. I am using Office 2000 Service Pack 3 with Windows 2000 Professsional Service Pack 4. Can anybody throw some light on this error. Thanks in anticipation. Michael What are you doing at the time (i.e. is it consistent)? -- Regards; Rob ----------------...

Data Migration Problem #2
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C4C0C4.D0290A10 Content-Type: text/plain; charset="big5" Content-Transfer-Encoding: quoted-printable Bonjour all, I have been struggling with the data migration problem for a long = time... and get stuck in the middle of the road. I am importing company data from either excel or text files into the = cdf_Product table of SQL server (cdf_mscrm database) using the SQL Query = Analyzer, where a problem is encountered that in the table structure, = there exists the product id as the primary key (data type: SQLI...

Ask 2 MSFT Experts About v8.0 Security
Hello all, Welcome to the first Business Solutions Community discussion held directly in the Microsoft Great Plains newsgroup. During the week of September 20th, Microsoft Escalation Engineer Kelly Youells and Microsoft Senior Development Consultant David Musgrave will answer your version 8.0 security questions directly in this newsgroup thread. How is this different than any other week in the newsgroup? While product experts like Kelly and David may frequent the newsgroup during their workweek to answer as many questions as they can, they don't necessarily *solicit* questions o...

Storing Data in Microsoft Excel
Hi I wonder if anyone out there would help I have excel, and I have some data which I update daily in the spreadsheet, I would like to create a macro, to copy all the data onto another page, and then, so that the next day I enter new set of data and when I press the same button it copies it all to the same page as the other data, but one row underneath (so that it doesn't override it) I know this can be done, but I cant remember how to do it. I did something in VB, active.offset but that's all I can remember if there is anyone that could help that would be brilliant All the be...

Sharebuilder account import error
Invalid .qif header error when trying to import .qif file from sharebuilder accounts. Anyone know of a fix or experienced this problem?? I typed a long mail to sharebuilder regarding this issue...still waiting fora reply. Hopefully they will be able to help. Microsoft flat out told me it wasn't their issue and to contact my broker! No help AT ALL. Microshaft..... OMalley ...

Shifting data
I have a very long column of data around 5000 cells long. Is there a way to shift just the even or just the odd cells to the next column? The evens are names.. the odds are offices. I need to be able to categorize them. There must be a way I just can't figure it out. Thanks in advance Tony, try- =IF(MOD(ROW(),2)=0,$A2,"") OR---- =IF(MOD(ROW(),2)=1,$A2,"") regards, JohnI "Tony Corneto" <tony@orbitaltours.com> wrote in message news:39d301c374c4$b8c3fd50$a401280a@phx.gbl... > I have a very long column of data around 5000 cells > long...

DPM2010 RC Errors on secondary server.
We have recently upraded our 2007 SP1 Primary DPM server to 2010 RC and it is working perfectly. I've set up a brand new, fresh Windows 2008 R2 Server for the secondary server, and have told it to protect one of the protection groups on the primary server. Everything works for a little while, but every half hour or so, the DPM service crashes and says: "Connection to the DPM service has been lost. Review the application event log for information about a possible service shutdown. ID: 917" Then asks to restart the administrator console. This kills the ...

database criteria
For the dmax example in excel help it shows the table below. It shows how to find the max profit of apple and pear trees, or the max profit of apple trees. Because the criteria requires a column label ("Tree"), is there any way to find the max profit of just pear trees? I tried dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label is not included in the criteria. Thanks! A B C D E F 1 Tree Height Age Yield Profit Height 2 Apple >10 <16 3 Pear 4 Tree Height Age Yiel...

ERROR
Hi I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the account numbers (which someone input, along with addresses) have a little green mark in the corner of the cell. I thought it was to show that there is a comment, but it is not. When you click on the cell, a little warning sign shows, saying ERROR - number in this cell formatted as text or preceded by an apostrophe. When I click on the arrow by the warning sign, it says Number stored as text Convert to number Ignore error Edit in formula bar Error checking options Show formula in audinting toolbar Now I looked a...

Outlook Express: emails printing too large
When I retrieve my emails they are easy to read. When I print the emails off the font is very large -- 14 pt or larger. What do I need to change to reduce the printable font to normal 10 pt size? ...

How to carry over date value from one record to the next.
I have searched this site and the internet in search of an answer to this problem but none of the solutions I've found have seemed to help. I have a form that is used for data entry. The first two text boxes are called Supervisor and W/E Date. These two values will remain the same for several dozen entries. I have tried just using CTRL + ' to repeat the data but that is getting quite time consuming as well. How do I automatically carry over the value to the next record once I input the first data in? Any help on this would be much appreciated. -- Message posted via AccessMo...

error, chkdsk
Questio, when I try to open excel or winmx, an error pops up saying chkdsk, so i ran a chkdsk and it doesnt do anything. The error also says corrupted file. In excel I have some important work that I need to open, and it only opens in Turkish, if I change it to English, its a bunch of boxes and letters, its not my work I need. How do I change it back. What excel version are you using? Try to find somebody with 2003 to see if they can open the file. Otherwise you would look for some commercial software that might be of help http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B30...

auto updating source data
Hi I have a matrice that looks something like this: A B 1 service 1000 2 maintenence 500 3 transport 300 4 service 200 5 6 7 8 and from this I like to make a pie-chart with procentage of the costs. The problem is that I never know how many rows I will have and I would like to make my chart's source- data updated automaticaly, so I would have to specify it manually each time. Is this possible? will be thankfull for any comments tnx ...

Access 2.0 Converter 09-27-04
I have installed the converter for moving from Access 2.0 to Access 2003 running under Windows XP Professional. However Access 2003 still tells me I need to convert the database and does not recognise the converter has been installed, so points me again to the Microsoft Website to obtain the converter. Any suggestions please. Hi Colin, My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups. Are you sure that you ran the .exe file for the Access 2.0 Converter so that it was installed? To download the Access 2.0 Converter free of charge, visit the following Micro...

Counting data in a range
I want to count a specific data item from a range e.g. say the data below was in column A and B on a spreadsheet: Department Name SALES John SALES Fred IT Sue FINANCE Stu SALES Jo HR Steve I want to count the number of times SALES occurs. I've tried DCOUNT/DCOUNTA but haven't got this to work. Any help appreciated! =COUNTIF(A:A,"sales") Vaya con Dios, Chuck, CABGx3 "Stuart" <Stuart@discussions.microsoft.com> wrote in message news:1D07D4B0-5824-4C11-8EF2-73CDF831C37F@microsoft.com......

4.4.7 error
I have problems on one client that they don't recieve all emails from people sending to them. I guess something is wrong in the configuration. When I do a nslookup -type=mx domain.com I just get unautharized answer. Also the ttl time is just 1800 when I do a dns report. Do the guys that handle the dns change this? The people that doesn't get their mail throw get the 4.4.7 error in return. Best regards Jimmy http://support.microsoft.com/kb/555375 -- Ed Crowley MVP "There are seldom good technological solutions to behavioral problems." .. "Surfer&q...

error 0x800CCC0F
Dear Team; I am receiving the following error messages, please assist Could result be sent to lewis.king@riotinto.com regards Lew Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. Subject 'brocolli & mushroom stir fry noodle', Account: 'pop.gmail.com', Server: 'smtp.gmail.com', Protocol: SMTP, Port: 995, Secure(SSL): Yes, Error Number: 0x800CCC0F Your server has unexpectedly terminated the connection. Possible causes for this include server p...

Excel error codes translations
hi, Does anybody know where I can find a kind of dictionary for Exce errors? The problem is sometimes I get an error message that is not in Englis and without error code and then looking for the solution I must kno its equivalent in English. Regards, Micha -- Message posted from http://www.ExcelForum.com ...

delete server...routing master error
hello, I have 2 exchange servers (exch 2003) and i have to delete one of them. I changed all replication etc...(before we had also a 5.5 which is already removed) but when i stop the services and try to remove the server via system manager of my second exchange (the one that will stay) i get an error message that my server that i want to delete is the routing master for that group. How can i make my other server the routing master ? i don't have any site replication (deleted it because not necessary anymore). thnx, Philippe Go to routing groups > first routing group > mem...

toolbar, date, services, errors, paste, minimized system restore doesn't work
I have a XP system that the toolbar does not show open programs, services do not seem to load at boot, the date is off by one month, I cannot paste anything that I copy, and system restore does not work. All of these issues started at the same time a few days ago. Has anyone else seen this group of errors? I am trying to resolve and do not know if it is a system update gone bad, or if maybe it is a viruse/malware issue. Please let me know if you had had these problems together and what you did to fix them. There is a very good chance that you are seeing the effects of a hi...

Re: Money 2004
Thanks for the suggestion. I tried unregistering/re-registering the msxml3 DLL, but still no luck. However, knowing the help file topics are local might help with debugging this problem. thanks, randy "Randy" <nospam@hotmail.com> wrote in message news:... > All, > > I've recently upgraded to Money 2004 Premium from 2003 Deluxe. I'm now > finding that I can't view any help topics--I get the "Unable to load topic. > Click here to try again." I've tried all the suggestions posted here and at > other sites: cleared out temporary in...

"File Error: Data May Be Lost" error when opening workbook saved in Mac
When I open in Excel 2007 for Windows workbook with charts saved in Mac OS, I see the message "File Error: Data May Be Lost". In Excel 2003 for Windows this message appears as many times as many sheets there are in workbook. Maybe someone knows what's the reason? Unfortunately you haven't provided the info needed... "Mac OS" means very little. The question is "What version of Mac Excel are you using, what file format are the files saved as & what type of content is in the files?" In general, what you're running into is that Excel 2003 &a...

Visual Studio 7 project subfolders #2
I know this may not be the exact group to ask this in, but in VS6.0 I used to be able to create subfolders within a project to keep my classes divided up conveniently. I just got VS7, however, and i can't seem to find any way to create subfolders. I can create subfolders for an entire SOLUTION, but not within a project. Am I missing or forgetting something here? Thanks! Duy Lam ...

Technical error
AMEX and Discover are not being sent over to the credit card processor during settlement. I comes up with a "TECHNICAL ERROR". Visa and MC are settling fine. Does anyone know what the problem is?? All are setup as different tranaction types in RMS and ALL authorize fine during the transaction process. Your merchant account is not correctly configured for AMEX & Discover. This is on the bank's side, not RMS. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me dir...

CObject Private method access error
Hi, I am trying to recompile application that has been developed in VC++ 6.0 with VC++2005.. While compiling, I'm getting the following error.. error C2248: 'CObject::CObject' : cannot access private member declared in class 'CObject' 1> c:\program files\microsoft visual studio 8\vc\atlmfc\include\afx.h(553) : see declaration of 'CObject::CObject' 1> c:\program files\microsoft visual studio 8\vc\atlmfc\include\afx.h(524) : see declaration of 'CObject' 1> This diagnostic occurred in the compiler generated function 'CExcepti...