Issues with date and salary

[siena]

Hello everyone,
I am trying to search our directory by, among other things, date and salary.

I have one column for date and one column for salary.
So far, I keep getting type mismath error on both salary and date.
Everything else seems to work.
Here is my code:

dateCheck = cdate(rs("fdate").value)
salary = rs("sal")

' Build our query based on the input.
strSQL = "SELECT id,min(incomeRevenue),max(incomeRevenue),companyName, address,city," _
& " state,zip_code,county,phone,faxnumber,incomeRevenue,employeeNumber, " _
& "headquarter,sic,naic, min(sal),max(sal),min(fdate),max(fdate) " _
& "FROM directorySearch" _
& "WHERE companyName LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR zip_code LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR phone LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR sal = '" & formatcurrency(salary,2) & " ' _
& "OR fdate = #" & dateCheck & "# _
& "GROUP BY id,companyName,address,city, state,zip_code,county,phone,faxnumber,incomeRevenue,employeeNumber,headquarter,sic,naic,fdate,sal " _
& "ORDER BY companyName;"


Your help is appreciated

 

[newmediaguy]

Hi

Try just a thought, try:

Salary = int(rs("sal"))

Thanks

Glen

Glen
Conception | Execution

 

[Tarwn]

Double check the field types in the databse for those two fields...

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[siena]

The field type is currency (access database).

The funny thing is I am even getting type mismatch in when I dim the variable:

Whether I dimmed like this:

Salary = rs("sal")

or like:

Salary = int(rs("sal"))

 

[onpnt]

I would not Format the numeric value to currency first. That may cause errors in the conversion to the access featured currency formatting.

And the error is more then likely coming from places apostrophes around the Salary var here

& "OR sal = '" & formatcurrency(salary,2) & " ' _

Currency is a numeric value thus no ' ' is needed

___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]

 

[siena]

Ok,
why am I getting type mismatch here:

Salary = rs("sal")

or like:

Salary = int(rs("sal"))

or like:
salary = CInt(rs("sal")

or salary = CLng(rs("sal")

 

[onpnt]

more then likly it is due to the SQL structure and what you are returning in that specific field (sal). to be honest. doesn't look like you're even asking for that field though.
IF -- you are selecting the sal columna dn returning it as a currency value in the table then it will work correctly.

e.g.
test

<%
Dim Salary

Salary = FormatCurrency("30000.50",2)

Salary = cDbl(Salary)

Response.Write FormatCurrency(Salary,2) & "<br>"

Response.Write cDbl(Salary) & "<br>"


Dim conn
Dim connStr
Dim JetSQL
Dim myRS

Set conn = Server.CreateObject("ADODB.Connection")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=\TekTips\TekTips.mdb;"

JetSQL = "SELECT Salary AS maxSalary FROM tblFormatting " & _
			"WHERE ident > 0 AND Salary = " & Salary & ";"
conn.Open(connStr)

Set myRS = conn.Execute(JetSQL)

If NOT myRS.EOF Then
	Response.Write cDbl(myRS("maxSalary"))
End If

conn.Close
Set myRS = Nothing
%>

___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]

 

[onpnt]

BTW: just because you ORDER BY the fdate and sal columns doesn' tmean it will be coming over in your rs. you still need to SELECT them further then the MIN() or MAX()

___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]

 

[siena]

Hi onpnt,
Your test code worked fine but the big question, though is why do I keep getting this type mismatch error on this line?

Salary = rs("sal")