Replies

Thanks Francois!!

That took care of it. Is that also the reason why I am having the problem with the “Update” action for the same command button?

Case “&Update”
sSQL = “UPDATE tblSample SET SDate=#” & Me.txtSdate & “#, ” & _
“AssocName='” & Me.cboAssocName & “‘, ” & _
“QARep='” & Me.cboQARep & “‘, ” & _
“Manager='” & Me.cboManager & “‘, ” & _
“Site='” & Me.cboSite & “‘, ” & _
“Department='” & Me.cboDepartment & “‘,” & _
“Arn=” & Me.txtARN & “,” & _
“ARNName=” & Me.txtARNName & “,” & _
“Function='” & Me.cboFunction & “‘, ” & _
“ErrorType='” & Me.cboErrorType & “‘, ” & _
“ErrWeight=” & Me.txtErrWgt & “, ” & _
“QANotes=” & Me.txtQANotes & “, ” & _
“WHERE SampleID = ” & Me.lstData & “;”
Debug.Print sSQL
CurrentDb.Execute sSQL
Call cmdClear_Click

Legare,

That solved my problem.

Thanks!

That is good stuff!! Thanks to all.

If I remove the WORKDATE is there a way that I can pull data for a particular date range?

Hans,

Have another ? for you about this query that I have built. The account numbers are showing mutiple times if the WORKDATE is different. This is causing my count of samples to exceed what it should be. I have set the query up to group as follows:

SELECT tblExpressQASample.WORKDATE, tblExpressQASample.Manager, Associate.AssocName, tblExpressQASample.AccountNumber, Count(tblExpressQASample.AccountNumber) AS Samples
FROM Associate INNER JOIN tblExpressQASample ON Associate.AssocID = tblExpressQASample.AssocID
GROUP BY tblExpressQASample.WORKDATE, tblExpressQASample.Manager, Associate.AssocName, tblExpressQASample.AccountNumber
ORDER BY Associate.AssocName;

Which is producing a result like this:

WORKDATE AssocName AccountNumber Samples
6/8/2005 Smith, John 1234 1
6/10/2005 Smith, John 1234 1

….and this is counting as 2 samples when it should be 1. Is there a way to remove the GROUP BY for WORKDATE so that I see the account number only once (probably not)? If not, is there a work around for this that you can think of?

Thanks

Hans,

This is great!!! Thanks again.

Oops…sorry that I did not attached the .xls. Question, when I added this formula to the cell, and the cell was formatted as a number, the end result was 0. If the cell was formatted as general, the formula appeared to calculate the entire range, and did not look at my date criteria (CF_DateOut,RDate). Is there a way to have the end result of this array provide me with an N/A?

Thanks Legare!

I knew there had to be a way to get AND in this formula. I modified yours slightly to get the result that I needed.

=IF(AND(ISBLANK(L3),ISBLANK(G3)),(SUM(M3-J3)),””)

Thanks again!

Hans,

There is a good portion of the information that you provided that I can put to use to resolve my problem. Thanks again!!!

Thanks again!!!

Hans,

I have another question on this fabulous formula that you created for me so that I could determine the beginning and ending funding date of each month.

5/30 was observed as a holiday (because it fell on Monday). I have defined a range of cells as “Holidays”, and have entered 5/30 n one of the cells. Am hoping that there is a way to add to the original EOM formula (=WORKDAY(EOMONTH(RDate,(RDate>WORKDAY(EOMONTH(RDate,0)+1,-2))+0)+1,-2,) so remove any Holidays that fall between BOM (Beginning of Month) and EOM(End of Month). I did try it some way and it backed the last funding day in the month to the 29th, and it s.b the 27th (as we do nto count weekends).

Thanks for any help that you can provide!!!

Mucho thanks!!!

Hans,

Is it possible to do the same thing without using VLOOKUPs? I am having problems adding to this formula:

=LOOKUP(IDate,Tran_Date,Jumbo_ND)

Fabulous, oh man that never rests!

I did have to re-define my named ranges from $A:$A to $A$##:$A$#### to get the formula to work. Why is that?

The man who never sleeps. Thanks again!!!!