SQL Statement

[alastair69]

I have a a SQL statement that clear out the data to replace it with random records, i need to set a criteria to look at a form and use a field to limit the number of records returned, i can do this manually but it is a pain to have to do it each time i run the Sql statement.

I have enclosed a copy of the sql code and will include field & form names to help.

[ContactType] = Calls Today or General Contacts plus others.

SQL CODE:-

strSQL = "SELECT TOP " & strTopVal & " ClientLookUp,ClientID, Address1LookUp, Address2LookUp,Address3LookUp,Address4LookUp," & _
" PostCode, Classification, ContactName, ContactPhone,ContactFax,WebAddress,Owner,Lab,Contractor,AccountRef,Status,TermsAgreed," & _
" ContactType,AccountManager FROM [JT Client List] " & _
" ORDER BY RandomNumber([ClientID]);"
Debug.Print strSQL
db.QueryDefs.Delete "qry Random Clients Temp"
Set qdf = db.CreateQueryDef("qry Random Clients Temp", strSQL)

I Like the success of this forum people seem happy to help, I have even managered to help peopl.

Alastair

 

[alastair69]

Got another problem getting a refresh or requery then do you use which

 

[alastair69]

Can anybody help me please

 

[alastair69]

anybody help me

 

[Fizzio]

i need to set a criteria to look at a form and use a field to limit the number of records returned,

strSQL = "SELECT TOP " & strTopVal & " ClientLookUp,ClientID,

Be patient!! remember most of the UK are either getting their beauty sleep or are enjoying their leisure time right now

As far as your question goes, I have quoted yo answering your own question!
You have set your number returned with your strTopVal variable so you have lost me a bit there.

Also, do not include spaces in names of objects - it is bad practice.

Now you have half an answer and I'm now going to go get some beauty sleep myself, so if this does not help, clarify your points and sit tight

 

[WayneRyan]

Alastair,

Reference a form control like the first line of the code.

strSQL = "SELECT TOP " & Me.SomeFormControl & _
         "       ClientLookUp,    " & _
         "       ClientID, " & _
         "       Address1LookUp, Address2LookUp, Address3LookUp, Address4LookUp," & _
         "       PostCode, " & _
         "       Classification, (and other fields...) " & _
         "FROM   [JT Client List] " & _
         "ORDER BY RandomNumber([ClientID]);"

I have a a SQL statement that clear out the data to replace it with random records.

Your original SQL statement doesn't alter the data at all. To change the data in
the table, you will need an Update (change) or Insert (Replace) query. Since the
original query doesn't modify your data, what does the RandomNumber function do?


Wayne