By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Inserting a Fixed Block of Text into a Procedure

P: 18
Currently this Module works placing "Members scores for 22/05/2012 out of a possible 200.020 were:" before the scores.
I would like to add a post script after the scores " Open from 6pm" if anyone can help...


Expand|Select|Wrap|Line Numbers
  1. Function ScoreRankingTotal(ByVal dtRank As Date, ByVal boolRanked As Boolean, Optional intTop As Integer = 0) As String
  2. Dim qdf As QueryDef
  3. Dim rs As Recordset
  4.  
  5. Dim strRank As String
  6.  
  7.  
  8. strRank = "Members scores for " & Format(dtRank, "dd/mm/yy") & "; out of a possible 200.020 were: "
  9.  
  10. Set qdf = CurrentDb.QueryDefs("20020")
  11. qdf.Parameters("[View Date]") = dtRank
  12.  
  13. Set rs = qdf.OpenRecordset
  14. Do While Not rs.EOF
  15.     strRank = strRank & rs.Fields("Member") & " " & rs.Fields("Total") & "; "
  16.     If rs.AbsolutePosition = intTop - 1 Then Exit Do
  17.     rs.MoveNext
  18. Loop
  19.  
  20. Set rs = Nothing
  21. Set qdf = Nothing
  22.  
  23. ScoreRankingTotal = strRank
  24.  
  25. End Function
  26.  
May 23 '12 #1

✓ answered by TheSmileyCoder

First off, a Module is what in VBA is used to store code. You can have a form module which is stored along with the form and bound to the form, or a seperate module, not attached to any form, but simply belonging to the application as a whole.

When you use the term Module incorrectly as you have done, you are seriously confusing the matter at hand.

Now to add any text to your string, you can modify line 23, with ONE of the following examples:

Expand|Select|Wrap|Line Numbers
  1. ScoreRankingTotal = strRank & vbnewline & "Open from 6pm"
  2. ScoreRankingTotal = strRank & "Open from 6 pm"
  3.  

Share this Question
Share on Google+
16 Replies


Rabbit
Expert Mod 10K+
P: 12,366
I have no idea what you're asking.
May 23 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
First off, a Module is what in VBA is used to store code. You can have a form module which is stored along with the form and bound to the form, or a seperate module, not attached to any form, but simply belonging to the application as a whole.

When you use the term Module incorrectly as you have done, you are seriously confusing the matter at hand.

Now to add any text to your string, you can modify line 23, with ONE of the following examples:

Expand|Select|Wrap|Line Numbers
  1. ScoreRankingTotal = strRank & vbnewline & "Open from 6pm"
  2. ScoreRankingTotal = strRank & "Open from 6 pm"
  3.  
May 23 '12 #3

P: 18
Apologies for my limited understanding of the terminology. Line 22 is blank, right? so I added your line 1 there.. Closed and Saved however the result was unchanged, I got the same when I tried your suggestion line 2. Am I confusing 'modify' with 'add'
May 24 '12 #4

P: 18
Sorry for not making it clear. This code acts on a Query to format up a list of members scores for the local newspaper. I have a block of common text I place in the beginning of each write up.
It is:
"Members scores for 22/05/2012 out of a possible 200.020 were:" The code produces a line that can be copied which reads ...
Members scores for 22/05/12; out of a possible 200.020 were: Rick Ruiterman 199.014; Paul Abbot 199.006; John Ball 196.014; Neil Brown 194.008; Irene Cameron 193.009; Tallulah Hedley 193.005; Bill Teddy 192.004; Jeremy Hedley 190.005; Des Connelly 189.004; Stuart Cameron 184.003; Ian Edwards 181.004; Julia Stevenson-Renwick 180.004; etc etc.
I was trying to add another block if text to the end of this saying "Open from 6pm" and needed help on writing this line of code and advise where to place it within the code sample provided above.
May 24 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Sorry, I made a typo, it was meant to say line 23.
May 24 '12 #6

ADezii
Expert 5K+
P: 8,638
@Paulo357
Expand|Select|Wrap|Line Numbers
  1. '************************ Code intentionally omitted ************************
  2. Do While Not rs.EOF
  3.   strRank = strRank & rs.Fields("Member") & " " & rs.Fields("Total") & "; "
  4.     If rs.AbsolutePosition = intTop - 1 Then Exit Do
  5.       rs.MoveNext
  6. Loop
  7.  
  8. strRank = Left$(strRank, Len(strRank) - 1)  'Remove Trailing ';'
  9.  
  10. 'Add a Blank Line, then desired Text
  11. strRank = strRank & vbCrLf & vbCrLf & "Open after 6:00 P.M."
  12. '************************ Code intentionally omitted ************************
  13.  
May 24 '12 #7

P: 18
Thanks SmileyCoder that worked
May 24 '12 #8

P: 18
ADezzi
I see your suggesting removing the trailing ;
Expand|Select|Wrap|Line Numbers
  1. strRank = Left$(strRank, Len(strRank) - 1)  'Remove Trailing ';'
  2.  
This did not work .. Iam still interested in getting that right if you can have a second look please
May 24 '12 #9

NeoPa
Expert Mod 15k+
P: 31,491
Paulo357:
This did not work - I am still interested in getting that right if you can have a second look please
You might want to start by explaining what didn't work in that case ;-) Something 'not working' is very little to go on.

Try this variation of ADezii's code :
Expand|Select|Wrap|Line Numbers
  1. '************************ Code intentionally omitted ************************
  2. Do While Not rs.EOF
  3.   strRank = strRank & "; " & rs!Member & " " & rs!Total
  4.   If rs.AbsolutePosition = intTop - 1 Then Exit Do
  5.   rs.MoveNext
  6. Loop
  7.  
  8. strRank = Mid(strRank, 3)  'Remove extra '; '
  9.  
  10. 'Add a Blank Line, then desired Text
  11. strRank = strRank & vbCrLf & vbCrLf & "Open after 6:00 P.M."
  12. '************************ Code intentionally omitted ************************
May 25 '12 #10

P: 18
Thanks NeoPa. Sorry thought I was being succinct. the code ran but didnt add the last line "Open after 6:00pm". re your code that did remove the trailing ";" thanks. I had to change the last reference from StrRank to ScoreRankingTotal. But it adds a blank line between the scores ending [now without a ; but I see now I'll need a fullstop. any ideas?
all the code looks like this now
Expand|Select|Wrap|Line Numbers
  1. Function ScoreRankingTotal(ByVal dtRank As Date, ByVal boolRanked As Boolean, Optional intTop As Integer = 0) As String
  2. Dim qdf As QueryDef
  3. Dim rs As Recordset
  4.  
  5. Dim strRank As String
  6.  
  7.  
  8. strRank = "Members scores for " & Format(dtRank, "dd/mm/yy") & "; out of a possible 200.020 were: "
  9.  
  10. Set qdf = CurrentDb.QueryDefs("20020")
  11. qdf.Parameters("[View Date]") = dtRank
  12.  
  13. Set rs = qdf.OpenRecordset
  14. Do While Not rs.EOF
  15.      strRank = strRank & "; " & rs!Member & " " & rs!Total
  16.   If rs.AbsolutePosition = intTop - 1 Then Exit Do
  17.   rs.MoveNext
  18. Loop
  19.  
  20. strRank = Mid(strRank, 3)  'Remove extra '; '
  21.  
  22. 'Add a Blank Line, then desired Text
  23. ScoreRankingTotal = strRank & vbCrLf & vbCrLf & "Open after 6:00 P.M."
  24. End Function
  25.  
I dont know if its related or not but the results is clipping off the first 2 letters of the start???
" mbers scores for 22/05/12; out of a possible 200.020 were"
should read... Members scores etc.. is that something your code has done or what?
Appreciate any help

Paul
May 25 '12 #11

NeoPa
Expert Mod 15k+
P: 31,491
Paul:
is that something your code has done or what?
Yes. I hadn't followed the whole sequence of the logic, but just ADezii's snippet.

Try instead :
Expand|Select|Wrap|Line Numbers
  1. Function ScoreRankingTotal(ByVal dtRank As Date, _
  2.                            ByVal boolRanked As Boolean, _
  3.                            Optional intTop As Integer = 0) As String
  4.     Dim db As DAO.Database
  5.     Dim strRank As String
  6.  
  7.     Set db = CurrentDb()
  8.     With db.QueryDefs("20020")
  9.         .Parameters("[View Date]") = dtRank
  10.         With .OpenRecordset
  11.             Do While Not .EOF
  12.                 strRank = strRank & "; " & !Member & " " & !Total
  13.                 If .AbsolutePosition = (intTop - 1) Then Exit Do
  14.                 Call .MoveNext
  15.             Loop
  16.         End With
  17.     End With
  18.  
  19.     strRank = "Members scores for %D; out of a possible 200.020 were: " & _
  20.               Mid(strRank, 3) & "%L%LOpen after 6:00 P.M."
  21.     strRank = Replace(strRank, "%D", Format(dtRank, "dd/mm/yy"))
  22.     ScoreRankingTotal = Replace(strRank, "%L", vbNewLine)
  23. End Function
PS. Indenting is not, and should never be, randomly applied. It is used to indicate which code is within a logical block. Indenting lines differently from others of the same logical block makes code very hard to follow. If you are not interested in indenting in a logical manner (I recommend you do, but if you choose not to), then I suggest you avoid indenting lines at all, as it simply confuses the vast majority of us for whom indenting is a very important aid to understanding the intent of the coder.
May 25 '12 #12

P: 18
Hi NeoPa
Thanks for the help...
I pasted your code into my module and it showed an error on line 22. That entire line was red. Ive never seen %L%LOpen and "%L" code in MS Access before I may not be running the appropriate References to handle it.. But Iam interested to give it a try as no one else is helping just now.
Thanks for pointing out the indenting thing.. I didn't know that and I will try to implement it as I often grab bit & pieces and plug them into my projects.
May 25 '12 #13

NeoPa
Expert Mod 15k+
P: 31,491
Try the code again. I'm sorry for the error, but line #22 was missing a closing parenthesis ")" (which has now been fixed).

"%L" is simply a string. %X markers are simply used in this code to mark a place in the string for non-literal values to be inserted (The function Replace() is found in the VBA library, so it's about as standard usage as you can get).

EG. Instead of using code-block A, I tend to use code-block B :
  1. Expand|Select|Wrap|Line Numbers
    1. Dim strMsg As String
    2.  
    3. strMsg = "The date today is :" & vbNewLine & _
    4.          Format(Date(), "d mmmm yyyy") & vbNewLine & vbNewLine & _
    5.          "Have a nice day!"
    6. Call MsgBox(strMsg)
  2. Expand|Select|Wrap|Line Numbers
    1. Dim strMsg As String
    2.  
    3. strMsg = "The date today is :%L%D%L%LHave a nice day!"
    4. strMsg = Replace(strMsg, "%L", vbNewLine)
    5. strMsg = Replace(strMsg, "%D", Format(Date(), "d mmmm yyyy"))
    6. Call MsgBox(strMsg)
You get the same result, but in block B you can see at a glance the type of string the coder is planning to produce. I use % simply as a matter of convenience. It's a visible character that doesn't appear too often in ordinary text. As you will see if you check out this most useful of string functions in the Help system (See Context-Sensitive Help), the replaced string (Parameter #2) can be any valid string. Typically I use % strings of some form, but it can also prove useful to handle strings that have not been prepared in any way.

EG. To get the name of a back-end Access database from a linked table ([tblX]), where the value of .Connect is, for instance, ";DATABASE=H:\Database\Data97.Mdb", you could use :
Expand|Select|Wrap|Line Numbers
  1. Dim strName As String
  2. Dim dbVar As DAO.Database
  3.  
  4. Set dbVar = CurrentDb()
  5. strName = Replace(dbVar.TableDefs("tblX").Connect, ";DATABASE=", "")
  6. Call MsgBox(strName)
May 26 '12 #14

P: 18
Ahh, thank you that helped a lot.
Ive been tinkering with trying to remove the new line between the scores and the postscript entry and including a full stop after the scores
Line 22
Expand|Select|Wrap|Line Numbers
  1. ScoreRankingTotal = Replace(strRank, "%L", ". ")
  2.  
This is giving me two full stops
I cant find the correct way to get one full stop and a single space before the postscript
Any thoughts?
regards paul
May 27 '12 #15

NeoPa
Expert Mod 15k+
P: 31,491
From what you've posted, I would say to use just the space in the replacement string.

The reason the first part of that is emphasised is because I actually suspect what you posted was too little information for the question. You should think about posting all the relevant code and the relevant data. That way I could answer more reliably without so much dependence on my guesses as to what you're really dealing with.
May 27 '12 #16

P: 18
Thanks you
I was entering my code on the wrong line. I left line 22 as it was and added to line 23 the following amendment and it worked.
Expand|Select|Wrap|Line Numbers
  1. Mid(strRank, 3) & "." & "%L%LOpen after 6:00 P.M."
  2.  
May 28 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.