Replies

Thanks for pointing me in the right direction. Most appreciated!

I have various dates in the detail section of a report. I want to concatenate each day part of the date into a field in the footer section. For example, if a client has meetings on 7/4/2014, 7/6/2014, and 7/20/2014, I want the days (4, 6, 20) to appear in a text box. I can get the day part of the dates, but I’m puzzled about placing the values. I thought about using a recordset and loop – not certain if that is the correct approach.

I can put the back end on my SkyDrive (front end on my PC) and it works fine (it’s a small database). My problem is allowing other users to connect their front end to my SkyDrive back end. I have read where SkyDrive Pro can allow this, but I have not seen any info on linking a back end. Oh well, another problem to solve. Thanks for the reply.

Thanks so much. It’s aggravating when MS fixes something that is working.

Dell Studio XPS 9100.

I thought about that, and I’ll give it a try.

Thanks for the reply,
Allan

Thanks for the reply, Wendell. You’re correct, there is more code. I have a Word 2003 document that gets the merge data from an Access 2003 table. The client needs to edit the wording in the merge document on occasion, so I would like to have a command button that would open the merge document. I’ve written the code, the document opens, but the data source is not linked. However, if I open the merge document from the File Open menu in Word 2003, I receive the warning about the SQL command and answer Yes to the prompt.

I’m curious why I don’t receive the warning when using the VBA code. I have a Select Case statement that determines which merge letter (there is more than one) the user selected in an option group and passes the document name to the strLetterName variable.

Code:

 
       On Error Resume Next
        MsgBox "Be sure to save document after editing", vbInformation, gcTitle
        Set WordApp = GetObject(, "Word.Application")

        If WordApp Is Nothing Then
            Set WordApp = CreateObject("Word.Application")
        End If
        WordApp.Visible = True
        
        gStrDocPath = "S:New_HiresLetters"
        strDoc = gStrDocPath & strLetterName
      
        
        Set WordDoc = WordApp.Documents.Open(gStrDocPath & strLetterName)

Just curious. If I cannot fix this I’ll have the client open the letter through the Word File menu.

Thanks again!
Allan

Thanks. I sometimes run out of ideas – never thought of having a table used twice. I’ll give it a try…

(My screen shot has different table names, but your suggestions is fine.)

Good point, John. That does make sense. I knew it was something practical that I was overlooking.

Thanks to both who replied. I’ll create a temporary table and add it to the query.

Thanks, John. I’ve pasted the SQL view into this post. I use an OR word to separate the values. More than one value can be used to return more than one account in the query. My text box would have 17133 OR 23456 in it, surrounded by quotes. It must be a text data type since an account number can contain letters.

SELECT tblCusip.Cusip, tblAccounts.AcctNo, tblAccounts.InvestorName, tblCusip.State, tblCusip.BondDescription, tblCusip.Coupon, tblCusip.Maturity, tblCusip.SPRating, tblCusip.MoodyRating, tblCusip.[Average Life], tblCusip.BondName, tblCusip.[Call day], qryTotalOwned.SumOfQty
FROM tblAccounts INNER JOIN (tblCusip INNER JOIN qryTotalOwned ON tblCusip.Cusip = qryTotalOwned.Cusip) ON tblAccounts.AcctNo = qryTotalOwned.AcctNo
WHERE (((tblAccounts.AcctNo)=[Forms]![frmChooseBondReport]![txtAccts]) AND ((tblCusip.BondQuantity)>0));

The SQL view is taken from the query, so it refers to a a form by using the Forms! type of entry. If I would use an SQL statement, I can change this. It’s just been a long day and I’m hoping to find an easy fix. I’ll do a search for other posts.

Thanks again

Thanks for the reply. You’ve helped me come to a solution….

My day is now a lot brighter since receiving your reply. It is very refreshing to have some other ideas, especially when given in a such an unselfish spirit.

So much to learn – people here make it possible.
Thanks to all!

Thanks, Gwenda. The only item I’m fuzzy on would be how to construct a table to hold the multipliers, etc. So many times I see people recommending NOT to use DLookup, and am curious as to any other method of finding these values….
I would use this if necessary. Just wondering if there is a better way.

Thank you Charlotte, as this is exactly what I will be doing. Most of these are payroll, health, FICA, etc. I just need a jumpstart on ideas as to how to allow a user to update the values, then I can write some functions or procedures to accomplish this.
Each teacher earns a salary based on their degree and number of years at the district. Most of the calculations are based on their gross pay, but they change from fiscal year to fiscal year(July 1 to June 30).
I need to go through all the records, update their years ( referred to a step ) adding one each year to a maximum of 18, then calculate the FICA, Health, etc. according to a schedule that is published by the teachers’ contract. The schedule lists 3 items: The degree, Number of Years (Step), and the Salary.

Thank you so much for the reply and any ideas….