 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / September 2005Type mismatch with DLookup
I'm looking in a table for a particular name (the one in a combo box on a form) that has today's date. I used the following code, but I'm getting a type mismatch. I was not surprised because I was guessing at the code. If DLookup("txtName", "tblSilTrans", "txtName = " & Chr$(39) & cboName & Chr$(39) And "datDate = " & Chr$(39) & Date & Chr$(39)) Then Can someone correct this? Thanks. Nick This might work. If DLookup("txtName", "tblSilTrans", "txtName = " & Chr$(39) & cboName & Chr$(39) & " And datDate = #" & Date & "#") Then  SignatureDuane Hookom MS Access MVP > I'm looking in a table for a particular name (the one in a combo box on a > form) that has today's date. I used the following code, but I'm getting a [quoted text clipped - 6 lines] > > Nick Note that if the Regional Settings have set the short date format to dd/mm/yyyy, that won't work for the first 12 days of each month. (okay, it'll work on January 1st, February 2nd, March 3rd and so on, so there are only 132 days when it won't!) More reliable is: If DLookup("txtName", "tblSilTrans", _ "txtName = " & Chr$(39) & cboName & _ Chr$(39) & " And datDate = " & _ Format(Date, "\#mm\/dd\/yyyy\#")) Then SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > This might work. > If DLookup("txtName", "tblSilTrans", [quoted text clipped - 11 lines] > > > > Nick Using your code with... If DLookup("txtName",.......) then gave me a type mismatch when the stmt was "true" - meaning it found a match in the table. It seemed to work fine if the stmt was false. Suggestions? > Note that if the Regional Settings have set the short date format to > dd/mm/yyyy, that won't work for the first 12 days of each month. (okay, [quoted text clipped - 26 lines] > > > > > > Nick Are you certain that cboName is bound to a text field? If so, what name were you using? If the names can have apostrophes in them (like O'Reilly), try replacing the Chr$(39) with Chr$(34). If the names can have both apostrophes and double quotes in them (like O'Reilly's "Auld Sod Pub"), you'll need to do a little more work. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Using your code with... > [quoted text clipped - 33 lines] > > > > > > > > Nick I took your code and did the following: dim x as variant x = DLookup("txtName"......) if x is Null then... X was never "Null" - even when the table it was searching was empty. > This might work. > If DLookup("txtName", "tblSilTrans", [quoted text clipped - 11 lines] > > > > Nick Try If IsNull(x) Then SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > I took your code and did the following: > dim x as variant [quoted text clipped - 18 lines] > > > > > > Nick I had tried that and to your other response, cboName is bound to a text field. > Try > [quoted text clipped - 27 lines] > > > > > > > > Nick Try Dim strWhere As String strWhere = "txtName = " & Chr$(39) & cboName & _ Chr$(39) & " And datDate = " & _ Format(Date, "\#mm\/dd\/yyyy\#") Debug.Print strWhere If DLookup("txtName", "tblSilTrans", _ strWhere) Then Go to the Debug window (Ctrl-G) and check what's printed for strWhere. Does it make sense? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I had tried that and to your other response, cboName is bound to a text >field. [quoted text clipped - 33 lines] >> > > > >> > > > Nick If DLookup("[txtName]", "tblSilTrans", "txtName = '" Me.cboName & "' And [datDate = #" Date "#") Then > I'm looking in a table for a particular name (the one in a combo box on a > form) that has today's date. I used the following code, but I'm getting a [quoted text clipped - 6 lines] > > Nick Copying what you sent gave me a syntax error - it highlighted the "Me" as the error > If DLookup("[txtName]", "tblSilTrans", "txtName = '" Me.cboName & > "' And [datDate = #" Date "#") Then [quoted text clipped - 9 lines] > > > > Nick > If DLookup("txtName", "tblSilTrans", "txtName = " & Chr$(39) & cboName > & Chr$(39) And "datDate = " & Chr$(39) & Date & Chr$(39)) Then Apart from the acute error, this is not a sensible query. If the name exists for today's date, then it will return a string value containing the same name you queried on. If there is no matching record, then it will return a null. In each case, you are expecting boolean value by putting it inside an If...Then clause. Try getting the logic right, and you can clarify and simplify it too. ' note correct quotes for delimiting the string, and ' reliable date format too. strWhere = "txtName = """ & cboName.Value & """ " & _ "AND datDate = " & Format(Date(), "\#yyyy\-mm\-dd\#") ' now see if there is a record or not If DCount("*", "tblSilTrans", strWhere)>0 Then ' there is a record DoSomething Else ' there isn't a record DoSomethingElse End If PS: I see you said that there was an error involving "Me", but you did not have any reference to the Me object in the code you posted! Hope that helps Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.