My Function is Off somehow

[George W. Jackson]

OK, I am trying to create a cross query using a function for the cross
cells. This query gets its info. from another query and inserts the info.
to a temperary table - only one record at a time in the temp. table. Each
time a new record is selected it overwrites this temp. table so that only
one record is there at all times. Anyhow, this function is supposed to open
the cross query up in Excel.

I am getting a "Type Mismatch" error and I can't figure it out at all.

Please help me,

George

 

[George W. Jackson]

I forgot to paste my function! Here it is:






Public Function FillFryday(selFryday As Date, selEmp As Integer) As String
Dim dbs As Database, rst As Recordset
Dim recnum, i, j As Integer
Dim SQLSTRING As String, DATESTRING, fillstring As String


If IsNull(selFryday) Then GoTo EndF:
DATESTRING = Month(selFryday) & "/" & Day(selFryday) & "/" &
Year(selFryday)
SQLSTRING = "SELECT DISTINCT projectfrydays.project FROM projectfrydays
WHERE ((fryday = #" & DATESTRING & "# AND employeename = " & selEmp & " ));"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLSTRING)
rst.MoveLast
rst.MoveFirst
recnum = rst.RecordCount
'MsgBox recnum
fillstring = rst.Fields(0)
If recnum = 1 Then GoTo Cont01
For i = 1 To recnum - 1
rst.MoveNext
fillstring = fillstring + Chr$(10) + rst.Fields(0)
Next i

Cont01:
FillFryday = fillstring
rst.Close
Set dbs = Nothing

EndF:
End Function

 

[John Vinson]

I am getting a "Type Mismatch" error and I can't figure it out at all.

Care to post your code, indicating the offending line?

Telepathy is a useful skill but tends to be unreliable...

 

[George W. Jackson]

I've got to say, John, you gave me a good laugh today. My code is posted
and the line that is highlighted is this one:

Set rst = dbs.OpenRecordset(SQLSTRING)

In otherwords, something in the SQLSTRING code is complicating

Thanks in advance

 

[John Vinson]

Set rst = dbs.OpenRecordset(SQLSTRING)

In otherwords, something in the SQLSTRING code is complicating

Nope, it's probably the rst.

Access97 used the DAO object model; A200x uses ADO by default. Both
these object models have Recordset objects... but they are DIFFERENT
objects.

Open the VBA editor and select Tools... References. Make sure that
Microsoft DAO x.xx Object Library (latest version) is checked. If you
won't be using ADOX, uncheck the ActiveX Data Objects reference. To be
on the safe side, use

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

 

[George W. Jackson]

Nope, it's probably the rst.

Access97 used the DAO object model; A200x uses ADO by default. Both
these object models have Recordset objects... but they are DIFFERENT
objects.

Open the VBA editor and select Tools... References. Make sure that
Microsoft DAO x.xx Object Library (latest version) is checked. If you
won't be using ADOX, uncheck the ActiveX Data Objects reference. To be
on the safe side, use

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

I have Office XP Professional at work and at home. wait...... OK I'm
back, and I have wonderful news. It is working! When I first started
this message to you I have gonna say something along the lines of "It
didn't work" and I was going to proceed in telling what me error
message was, because it was different. No matter now, I had to
comment out the original DIM statements, DUH! So if you don't mind,
How would I check to see if I'm using A200x? This database was
created with Access97 and I used 2003 to convert it. Also, how am I
supposed to know if I'm using ADOX? Just curious, I have allready
unchecked the ActiveX Data Objects and all is well.

I want to thank you very very much.

Sincerely,

George W. Jackson

 

[John Vinson]

So if you don't mind,
How would I check to see if I'm using A200x?

Well, you are running Access 2003 it would appear. That's all I mean -
2000, 2002 and 2003 all use ADOX by default; 97 uses DAO by default.

This database was
created with Access97 and I used 2003 to convert it. Also, how am I
supposed to know if I'm using ADOX?

Generally, if you are using it you are intentionally doing so (unless
you've gotten ADOX code from someone else, and if they're any good it
should be clear and should include the Dim rs AS ADOX.Recordset
syntax.

Just curious, I have allready
unchecked the ActiveX Data Objects and all is well.
Good!

I want to thank you very very much.

You're most welcome.