I am trying to select the top n of records. n equals [Form]![frmAdminEntry]![text26]

my sql statement:

SELECT TOP [n] tblMeterData.Model & ", SN: " & tblMeterData.SN AS Hdw, tblMeterData.Date, tblMeterData.[1stMeter], tblMeterData.[1stMtrRollover], tblMeterData.[2ndMeter], tblMeterData.[2ndMtrRollover]
FROM tblMeterData
WHERE ((([tblMeterData].[Model] & ", SN: " & [tblMeterData].[SN]) Like [Forms]![frmAdminEntry]![Text7])) & [n] = [Form]![frmAdminEntry]![text26]
ORDER BY tblMeterData.Date DESC;

How can I get something like this to work? The query is returning the Last n of Meter Readings entered for a machine.

Thanks
matthew

What error are you getting?
In the WHERE Clause should it be AND n=Forms! etc. (not Form! by the way).

If this is excuted in a module, you probably don't need all the brackets either

Why don't you run this as a saved query instead of SQL?

To see the outcome of this SQL run it in a module and do a debug.print on the string. That helps to point up errors.

Don't put N in the WHERE clause. Instead,
Sub RunMySQL
Dim db as database
dim rs as recordset
Dim strSQL as String
Dim Loops as integer
Dim MyTopCount as Integer

Set db=CurrentDB

MyTopCount=Forms![frmAdminEntry]![text26]


strSQL="SELECT TOP " & MyTopCount & " tblMeterData.Model" & '", SN: "' & "tblMeterData.SN AS Hdw, tblMeterData.Date, tblMeterData.[1stMeter], tblMeterData.[1stMtrRollover], tblMeterData.[2ndMeter], tblMeterData.[2ndMtrRollover]
FROM tblMeterData
WHERE tblMeterData.Model" & '", SN: "' & "[tblMeterData].[SN]) Like Forms]frmAdminEntry![Text7]ORDER BY tblMeterData.Date DESC"

Debug.Print strSQL 'set a breakpoint here
set rs=db.openrecordset(strSQL)
With rs
.MoveFirst
for Loops=1 to MyTopCount
debug.Print !Model
.MoveNext
NextLoops
end With
End Sub

This will test your string. You will probably want to display the records in another way, but see if this works first


[This message has been edited by cogent1 (edited 06-06-2002).]

[This message has been edited by cogent1 (edited 06-06-2002).]

I will give this a try next week if not tomorrow. One of our stores network is down hard.
I've never used sql statements in VBA before but I'm looking forward to the challenge.
Thanks for your help