search criteria TYPE MISMATCH 1

[lalee5]

I have problems with the following search criteria: it is giving me a type mismatch error when I run the program.

Searchcriteria = "DATE=" & todaydate.Text And "socialsecno = " & txtpassword.Text

I want only the record matching the above criteria to show. there is only one record per employee. once the record match I want to be able to update fields in that record and update the record again.

Please help me!!!


thanks


kay

 

[williamu]

Use:

Searchcriteria = "DATE='" & todaydate.Text "'" + And ...

Missing ' from the date IIRC.



William
Software Engineer
ICQ No. 56047340

 

[lalee5]

I try the following and it comes back syntax error


Searchcriteria = "DATE='" & todaydate.Text "'" And "socialsecno = " & txtpassword.Text


if I put the + in front of and it comes back

Complie error: expected end of statement

Searchcriteria = "DATE='" & todaydate.Text "'" +And "socialsecno = " & txtpassword.Text



Can you show the whole statement

Kay

 

[zemp]

Try this,

Searchcriteria = "DATE='" & todaydate.Text & "' And socialsecno = " & txtpassword.Text

Also ensure that the date is of the corrrect type. If the database field is of type date you may need to convert the textbox value to a date type.

If the Social insurance number is a text field it will also need quotes. Otherwise it may need to be converted to a number.


zemp

 

[williamu]

The statement should be:

Searchcriteria = "DATE='" + todaydate.Text + "'" + " And socialsecno = " & txtpassword.Text

Assuming that txtpassword.text is is Number. If it's not then:

Searchcriteria = "DATE='" + todaydate.Text + "'" + " And socialsecno = '" + txtpassword.Text + "'"

Also the use of the Word date as a field name is not advisable as it may be reserved by the db.


William
Software Engineer
ICQ No. 56047340

 

[MakeItSo]

DATE is a reserved word. It's not so problematic as long as you work within the DB app itself, but addressing it from ASP or some VB code might cause problems.
Try using

Searchcriteria = "[b][[/b]DATE[b]][/b]='" &...&"'"

If it's an Access database, you might as well need

Searchcriteria = "[DATE]=[b]#[/b]" &...& "#"

Else you should consider renaming the field.

Hope that helps,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[lalee5]

since txtpassword is a numeric field I used the following statement but there is a new error: oh, I rename date to clockindate.

Searchcriteria = "clockindate='" + todaydate.Text + "'" + " And socialsecno = " & txtpassword.Text

erorr at runtime:


arguments are of the wrong type, are out of acceptable range or are in conflict with one another.

 

[strongm]

Um...which bit of williamu's advice was a problem? Put quotes around txtpassword.text and this particular problem should be fixed

 

[lalee5]

Still the same error after quotes. please see what I am doing wrong:

Searchcriteria = "clockindate='" + todaydate.Text + "'" + " And socialsecno = '" & txtpassword.Text & "'"

erorr at runtime:


arguments are of the wrong type, are out of acceptable range or are in conflict with one another.

 

[lalee5]

The error is stopping on the call statement after the criteria.

Call Adohours.Recordset.Find(Searchcriteria)

it seems like the criteria works but to call it so the current record is display that is where the problem is.


kay

 

[williamu]

Ah! You may well be using Constants for the RecordSet, Command object etc, these like adCmdText, adCmdTable possibly incorrectly set. Double check your settings.



William
Software Engineer
ICQ No. 56047340

 

[johnwm]

Using + to concatenate strings can lead to unexpected results - always use the concatenation operator & to join strings.
Insert the line :[tt]
Debug.Print Searchcriteria[/tt]
immediately before:
[tt]Call Adohours.Recordset.Find(Searchcriteria)
[/tt]
and post the results of the immediate window here.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[lalee5]

below is the code I try to get it to work.

the search criteria results are

searchcriteria="clockindate=06/27/2004 and socialsecno=586565441"


Code:

Dim Searchcriteria As String


Adohours.Recordset.MoveFirst

Searchcriteria = " clockindate = " + todaydate.Text + " And socialsecno = " & txtpassword.Text


Call Adohours.Recordset.Find(Searchcriteria)



it is stoping at Call Adohours.Recordset.Find(Searchcriteria)


with the error:

erorr at runtime:


arguments are of the wrong type, are out of acceptable range or are in conflict with one another.

 

[williamu]

This should be:

searchcriteria="clockindate='06/27/2004' and socialsecno=586565441"

Note the use of the ' around the date.


William
Software Engineer
ICQ No. 56047340

 

[lalee5]

William, I got the same as you when I add the quote to the statement but it stills crush at the call procedure.

its there ways arounf this?


Kay

 

[johnwm]

You appear to be missing the date delimiters. Find is used in several contexts but ADO usage requires # delimiters for dates

The value in Criteria may be a string, floating-point number, or date. String values are delimited with single quotes or "#" (number sign) marks (for example, "state = 'WA'" or "state = #WA#"). Date values are delimited with "#" (number sign) marks (for example, "start_date > #7/22/97#").

Try:
Searchcriteria = " clockindate = #" & todaydate.Text & "# And socialsecno = " & txtpassword.Text

Check that the socialsecno field is numeric, if not use single quote delimiters around the txtpassword.Text:

Searchcriteria = " clockindate = #" & todaydate.Text & "# And socialsecno = '" & txtpassword.Text & "'"

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[williamu]

Please refer to my comment above johnwm's above as regards the constants.

Me thinks that's where your problem lies.



William
Software Engineer
ICQ No. 56047340

 

[lalee5]

I think the search criteria works. it is just in the call procedure.

It seems like this will not work. is there another way to pull a record based on mulitple criteria make changes and update the record.

it there is a better way please direct me.


thanks

kay

 

[Swi]

It only supports one criteria.

http://support.microsoft.com/default.aspx?scid=kb;en-us;195222&Product=mdac

Swi

 

[williamu]

Well spotted Swi. You deserve this.

William
Software Engineer
ICQ No. 56047340