Code help required

J

Joe

I have been using the following code from Allen Browne to select a random
percentage of records on the open event of an unbound report.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*
FROM TblAssets ORDER BY Rnd([AssetID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = strSql
End If
End Sub

The code works well but I am struggling to write functioning code to
retrieve random records that meet a particular criteria. I can use a query
to select the records required and then build a report from that but I like
the idea of using code directly in the report. Please note that I am only
just learning to use SQL. The code I have tried so far is –

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String
Dim strWhere As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*â€
strWhere = "TblAssets.SCAItem = -1"
FROM TblAssets ORDER BY Rnd([AssetID]);

Else
strMsg = "Percentage must be between 1 and 100."
End If
Etc
I keep getting a compile error on the FROM line. I’ve researched all the
discussion groups and the web without finding an answer to my problem. Any
help would be very much appreciated to get me on the right track. Thanks in
anticipation.

Joe
 
R

Rob Parker

Hi Joe,

You need both the FROM clause and the WHERE clause as part of the SQL
string. And, if you break the line while entering this in code, you need to
include the line continuation sequence (a space followed by an underscore)
at the end of each line. Here's a short version which should not wrap in
most newsreaders:
strSql = "SELECT TOP " & strPercentage _
& " PERCENT TblAssets.*" _
& " FROM TblAssets" _
& " WHERE TblAssets.SCAItem = -1" _
& " ORDER BY Rnd([AssetID]);"

The aim is to build strSQL as a valid SQL statement which can be used as the
report's recordsource. If you include a
Debug.Print strSQL
statement immediately after this (compound) line defining strSQL, it will
print the result in the immediate window of the VBA editor, so you can check
that it is correct; you can copy/paste it from there to a query, and run the
query, to ensure that it is producing te results you expect.

HTH,

Rob

Joe said:
I have been using the following code from Allen Browne to select a random
percentage of records on the open event of an unbound report.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*
FROM TblAssets ORDER BY Rnd([AssetID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = strSql
End If
End Sub

The code works well but I am struggling to write functioning code to
retrieve random records that meet a particular criteria. I can use a
query
to select the records required and then build a report from that but I
like
the idea of using code directly in the report. Please note that I am only
just learning to use SQL. The code I have tried so far is -

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String
Dim strWhere As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*"
strWhere = "TblAssets.SCAItem = -1"
FROM TblAssets ORDER BY Rnd([AssetID]);

Else
strMsg = "Percentage must be between 1 and 100."
End If
Etc
I keep getting a compile error on the FROM line. I've researched all the
discussion groups and the web without finding an answer to my problem.
Any
help would be very much appreciated to get me on the right track. Thanks
in
anticipation.

Joe
Click to expand...
 
J

Joe

Rob thanks for your prompt reply. The code you supplied works really well!
I'm not sure I understand why the ampersands are used - please could you
explain? Also, is it possible to link two tables together (ie an INNER Join)
and how would this be achieved?

Many thanks,

Joe

Rob Parker said:
Hi Joe,

You need both the FROM clause and the WHERE clause as part of the SQL
string. And, if you break the line while entering this in code, you need to
include the line continuation sequence (a space followed by an underscore)
at the end of each line. Here's a short version which should not wrap in
most newsreaders:
strSql = "SELECT TOP " & strPercentage _
& " PERCENT TblAssets.*" _
& " FROM TblAssets" _
& " WHERE TblAssets.SCAItem = -1" _
& " ORDER BY Rnd([AssetID]);"

The aim is to build strSQL as a valid SQL statement which can be used as the
report's recordsource. If you include a
Debug.Print strSQL
statement immediately after this (compound) line defining strSQL, it will
print the result in the immediate window of the VBA editor, so you can check
that it is correct; you can copy/paste it from there to a query, and run the
query, to ensure that it is producing te results you expect.

HTH,

Rob

Joe said:
I have been using the following code from Allen Browne to select a random
percentage of records on the open event of an unbound report.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*
FROM TblAssets ORDER BY Rnd([AssetID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = strSql
End If
End Sub

The code works well but I am struggling to write functioning code to
retrieve random records that meet a particular criteria. I can use a
query
to select the records required and then build a report from that but I
like
the idea of using code directly in the report. Please note that I am only
just learning to use SQL. The code I have tried so far is -

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String
Dim strWhere As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*"
strWhere = "TblAssets.SCAItem = -1"
FROM TblAssets ORDER BY Rnd([AssetID]);

Else
strMsg = "Percentage must be between 1 and 100."
End If
Etc
I keep getting a compile error on the FROM line. I've researched all the
discussion groups and the web without finding an answer to my problem.
Any
help would be very much appreciated to get me on the right track. Thanks
in
anticipation.

Joe
Click to expand...
Click to expand...
 
R

Rob Parker

Hi Joe,

The ampersands are concatenation operators; they are used to build the final
strSQL string by joining together a series of sub-strings, each of which is
delimited with double-quote characters. In the string you are building,
they also are used to put the value of strPercentage into the final string.
You could also write the statement I supplied in a single line, thus:
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.* FROM
TblAssets WHERE TblAssets.SCAItem = -1 ORDER BY Rnd([AssetID]);"

That will almost certainly wrap in your newsreader! I find it easier to
follow, and more readable, to break the statement into sub-strings and
concatenate them, but it doesn't really matter. In the one-line example
above, you do need the concatenation operators (ampersands) to get the value
of strPercentage into the string.

As for linking two (or more) tables, sure. The easiest way is to use the
query design grid to build your query, setting the join(s) as you want. Run
the query to make sure it gives the results you expect. Then change to SQL
view, and cut/paste the SQL string into your code, delimiting it with
double-quotes, and replacing any parameters, etc with values from your code
(or references to controls o a form, or ...). Make sure that, if the SQL
view of the query is on more than one line (and it usually is, except for
very simple queries), that you include the line continuation characters when
you paste it into your code, or you'll get the problem you had previously;
and, if you want to keep things clear, use additional double-quote
delimiters and ampersands. The code editor will let you know when you've
got things wrong (missing continuation characters, missing ampersands,
unmatched delimiters), since the line will be shown in red ;-)

Again, HTH,

Rob


Joe said:
Rob thanks for your prompt reply. The code you supplied works really
well!
I'm not sure I understand why the ampersands are used - please could you
explain? Also, is it possible to link two tables together (ie an INNER
Join)
and how would this be achieved?

Many thanks,

Joe

Rob Parker said:
Hi Joe,

You need both the FROM clause and the WHERE clause as part of the SQL
string. And, if you break the line while entering this in code, you need
to
include the line continuation sequence (a space followed by an
underscore)
at the end of each line. Here's a short version which should not wrap in
most newsreaders:
strSql = "SELECT TOP " & strPercentage _
& " PERCENT TblAssets.*" _
& " FROM TblAssets" _
& " WHERE TblAssets.SCAItem = -1" _
& " ORDER BY Rnd([AssetID]);"

The aim is to build strSQL as a valid SQL statement which can be used as
the
report's recordsource. If you include a
Debug.Print strSQL
statement immediately after this (compound) line defining strSQL, it will
print the result in the immediate window of the VBA editor, so you can
check
that it is correct; you can copy/paste it from there to a query, and run
the
query, to ensure that it is producing te results you expect.

HTH,

Rob

Joe said:
I have been using the following code from Allen Browne to select a
random
percentage of records on the open event of an unbound report.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT
TblAssets.*
FROM TblAssets ORDER BY Rnd([AssetID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = strSql
End If
End Sub

The code works well but I am struggling to write functioning code to
retrieve random records that meet a particular criteria. I can use a
query
to select the records required and then build a report from that but I
like
the idea of using code directly in the report. Please note that I am
only
just learning to use SQL. The code I have tried so far is -

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String
Dim strWhere As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*"
strWhere = "TblAssets.SCAItem = -1"
FROM TblAssets ORDER BY Rnd([AssetID]);

Else
strMsg = "Percentage must be between 1 and 100."
End If
Etc
I keep getting a compile error on the FROM line. I've researched all
the
discussion groups and the web without finding an answer to my problem.
Any
help would be very much appreciated to get me on the right track.
Thanks
in
anticipation.

Joe
Click to expand...
Click to expand...
Click to expand...
 
J

Joe

Thanks again Rob, I have a much better understanding of what I need to do
and, more importantly, why. Time now to put this new knowledge into action.
Your help has been very valuable and is much appreciated.

Joe

Rob Parker said:
Hi Joe,

The ampersands are concatenation operators; they are used to build the final
strSQL string by joining together a series of sub-strings, each of which is
delimited with double-quote characters. In the string you are building,
they also are used to put the value of strPercentage into the final string.
You could also write the statement I supplied in a single line, thus:
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.* FROM
TblAssets WHERE TblAssets.SCAItem = -1 ORDER BY Rnd([AssetID]);"

That will almost certainly wrap in your newsreader! I find it easier to
follow, and more readable, to break the statement into sub-strings and
concatenate them, but it doesn't really matter. In the one-line example
above, you do need the concatenation operators (ampersands) to get the value
of strPercentage into the string.

As for linking two (or more) tables, sure. The easiest way is to use the
query design grid to build your query, setting the join(s) as you want. Run
the query to make sure it gives the results you expect. Then change to SQL
view, and cut/paste the SQL string into your code, delimiting it with
double-quotes, and replacing any parameters, etc with values from your code
(or references to controls o a form, or ...). Make sure that, if the SQL
view of the query is on more than one line (and it usually is, except for
very simple queries), that you include the line continuation characters when
you paste it into your code, or you'll get the problem you had previously;
and, if you want to keep things clear, use additional double-quote
delimiters and ampersands. The code editor will let you know when you've
got things wrong (missing continuation characters, missing ampersands,
unmatched delimiters), since the line will be shown in red ;-)

Again, HTH,

Rob


Joe said:
Rob thanks for your prompt reply. The code you supplied works really
well!
I'm not sure I understand why the ampersands are used - please could you
explain? Also, is it possible to link two tables together (ie an INNER
Join)
and how would this be achieved?

Many thanks,

Joe

Rob Parker said:
Hi Joe,

You need both the FROM clause and the WHERE clause as part of the SQL
string. And, if you break the line while entering this in code, you need
to
include the line continuation sequence (a space followed by an
underscore)
at the end of each line. Here's a short version which should not wrap in
most newsreaders:
strSql = "SELECT TOP " & strPercentage _
& " PERCENT TblAssets.*" _
& " FROM TblAssets" _
& " WHERE TblAssets.SCAItem = -1" _
& " ORDER BY Rnd([AssetID]);"

The aim is to build strSQL as a valid SQL statement which can be used as
the
report's recordsource. If you include a
Debug.Print strSQL
statement immediately after this (compound) line defining strSQL, it will
print the result in the immediate window of the VBA editor, so you can
check
that it is correct; you can copy/paste it from there to a query, and run
the
query, to ensure that it is producing te results you expect.

HTH,

Rob

I have been using the following code from Allen Browne to select a
random
percentage of records on the open event of an unbound report.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT
TblAssets.*
FROM TblAssets ORDER BY Rnd([AssetID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = strSql
End If
End Sub

The code works well but I am struggling to write functioning code to
retrieve random records that meet a particular criteria. I can use a
query
to select the records required and then build a report from that but I
like
the idea of using code directly in the report. Please note that I am
only
just learning to use SQL. The code I have tried so far is -

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String
Dim strWhere As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*"
strWhere = "TblAssets.SCAItem = -1"
FROM TblAssets ORDER BY Rnd([AssetID]);

Else
strMsg = "Percentage must be between 1 and 100."
End If
Etc
I keep getting a compile error on the FROM line. I've researched all
the
discussion groups and the web without finding an answer to my problem.
Any
help would be very much appreciated to get me on the right track.
Thanks
in
anticipation.

Joe
Click to expand...
Click to expand...
Click to expand...
 
R

Rob Parker

You're welcome.

Good luck with your endeavours. And you'll always find help in these
newsgroups.

Rob

Joe said:
Thanks again Rob, I have a much better understanding of what I need to do
and, more importantly, why. Time now to put this new knowledge into
action.
Your help has been very valuable and is much appreciated.

Joe

Rob Parker said:
Hi Joe,

The ampersands are concatenation operators; they are used to build the
final
strSQL string by joining together a series of sub-strings, each of which
is
delimited with double-quote characters. In the string you are building,
they also are used to put the value of strPercentage into the final
string.
You could also write the statement I supplied in a single line, thus:
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.* FROM
TblAssets WHERE TblAssets.SCAItem = -1 ORDER BY Rnd([AssetID]);"

That will almost certainly wrap in your newsreader! I find it easier to
follow, and more readable, to break the statement into sub-strings and
concatenate them, but it doesn't really matter. In the one-line example
above, you do need the concatenation operators (ampersands) to get the
value
of strPercentage into the string.

As for linking two (or more) tables, sure. The easiest way is to use the
query design grid to build your query, setting the join(s) as you want.
Run
the query to make sure it gives the results you expect. Then change to
SQL
view, and cut/paste the SQL string into your code, delimiting it with
double-quotes, and replacing any parameters, etc with values from your
code
(or references to controls o a form, or ...). Make sure that, if the SQL
view of the query is on more than one line (and it usually is, except for
very simple queries), that you include the line continuation characters
when
you paste it into your code, or you'll get the problem you had
previously;
and, if you want to keep things clear, use additional double-quote
delimiters and ampersands. The code editor will let you know when you've
got things wrong (missing continuation characters, missing ampersands,
unmatched delimiters), since the line will be shown in red ;-)

Again, HTH,

Rob


Joe said:
Rob thanks for your prompt reply. The code you supplied works really
well!
I'm not sure I understand why the ampersands are used - please could
you
explain? Also, is it possible to link two tables together (ie an INNER
Join)
and how would this be achieved?

Many thanks,

Joe

:

Hi Joe,

You need both the FROM clause and the WHERE clause as part of the SQL
string. And, if you break the line while entering this in code, you
need
to
include the line continuation sequence (a space followed by an
underscore)
at the end of each line. Here's a short version which should not wrap
in
most newsreaders:
strSql = "SELECT TOP " & strPercentage _
& " PERCENT TblAssets.*" _
& " FROM TblAssets" _
& " WHERE TblAssets.SCAItem = -1" _
& " ORDER BY Rnd([AssetID]);"

The aim is to build strSQL as a valid SQL statement which can be used
as
the
report's recordsource. If you include a
Debug.Print strSQL
statement immediately after this (compound) line defining strSQL, it
will
print the result in the immediate window of the VBA editor, so you can
check
that it is correct; you can copy/paste it from there to a query, and
run
the
query, to ensure that it is producing te results you expect.

HTH,

Rob

I have been using the following code from Allen Browne to select a
random
percentage of records on the open event of an unbound report.

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT
TblAssets.*
FROM TblAssets ORDER BY Rnd([AssetID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = strSql
End If
End Sub

The code works well but I am struggling to write functioning code to
retrieve random records that meet a particular criteria. I can use
a
query
to select the records required and then build a report from that but
I
like
the idea of using code directly in the report. Please note that I
am
only
just learning to use SQL. The code I have tried so far is -

Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
Dim strPercentage As String
Dim strMsg As String
Dim strWhere As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
strSql = "SELECT TOP " & strPercentage & " PERCENT TblAssets.*"
strWhere = "TblAssets.SCAItem = -1"
FROM TblAssets ORDER BY Rnd([AssetID]);

Else
strMsg = "Percentage must be between 1 and 100."
End If
Etc
I keep getting a compile error on the FROM line. I've researched
all
the
discussion groups and the web without finding an answer to my
problem.
Any
help would be very much appreciated to get me on the right track.
Thanks
in
anticipation.

Joe
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Match style 0
VBA Export to PDF 0
Word macro that can call cells out of Excel for a find and replace 0
Working outlook via vba code with xlsx - error 0
VBA Coding Help for Beginner 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Making a macro only work on certain files 2

Top