Search results

JHB, thanks for the tip. Galaxiom, your suggestion regarding a query is spot-on - I have the whole deal set up with little grief using the Switch function. However, while I admit I need lots of advice on Access, and for that matter, using the forum, your assumption that I am being dishonest...

The typo's in my post but not my code. Still get the overflow error.

There are no Null values in MemberExpire unless the code is operating on a new record, which in theory I've prevented. Is this what you eant by the use of the Nz function? Dim DateGap As Integer Dim Flag As Variant While Me.CurrentRecord < Me.Recordset.RecordCount Flag =...

Hello: I am trying to automate a membership status flag based on comparing today's date and a recorded expiry date. The expiry date control is on a sub-form. I have the following code in the OnLoad event of the master form: Dim DateGap As Integer While Me.CurrentRecord <...

That works perfectly, CJ, thank you very much. I'll have to spend some time studying the difference in the sub-query statement to understand why.

No, I want to put a flag on the MembersT record corresponding to the most recent record on MembersT (i.e. highest MemberCardNo) just for the individual from ContactMasterT whose MembersT records can be seen in a subform. So each update query would only update one record in MembersT. The...

That still updates 1414 records, unfortunately.

I figured out that my 1414 updated records represented the MembersT record with the highest MemberCardNo for each unique MasterID in MembersT. You are right, the sub-query works properly when tested for a specific MasterID. MemberCardNo is in MembersT, however. When I made a slight...

So, trying to absorb two pieces of advice, the update query is now coded as follows: Dim SQL As String SQL = "UPDATE ContactMasterT " & _ "INNER JOIN MembersT ON ContactMasterT.MasterID=MembersT.MasterID " & _ "SET MemberSpecial = 1 " & _ "WHERE MembersT.[MemberCardNo] "...

Actually, I just realized I am getting an update to one record, but it is not the correct one. It appears to be updating the MembersT record with the highest value of MasterID in the whole file, rather than the highest value of MemberCardNo among the records selected for a given individual in...

Thanks CJ. I think, however, my understanding of aliasing is deficient. Per your note, yes, my intent is just to update the most recent membership record. My code after your suggestion is: UPDATE ContactMasterT INNER JOIN MembersT ON ContactMasterT.MasterID=MembersT.MasterID SET...

Hi there: My database is set up so membership information for a selected contact is displayed in a subform. I am trying to run an update query from a command button macro to select the subform data file record for the most recent membership record and set a field on that record to a value of...

I am using Chr(13) & Chr(10) in the control, which inserts a line at what I presume is the line spacing appropriate to the font size, but I could use the extra space that would come from reducing the height of the line feed.

Is anyone aware of a way to insert a half-line feed into text in a control?

That's got it, thanks Paul.

The syntax error was a missing close bracket in FCriterion, making the filter contents: select * from ContactMasterT where Not(Replace(LastName, chr(39), '')) IS NULL AND Replace(LastName, chr(39), '') LIKE '*August*' ... but the type mismatch error is back. If it is Replace that is choking...

Paul - Debug.Print produces no output in this case.

You are right, the problem is the nulls. When I add "NOT (LastName) IS NULL" to the where clause it filters correctly. My problem now is passing the variable name from the search form into the actual filter. I am getting a syntax error from the following code: Private Sub cmdSearch_Click()...

I'm clearly out of my depth here, but... The code below does not generate any syntax error messages, but now produces error 13, "Type mismatch". Would not the If conditions prevent null values from reaching the select statement? Private Sub cmdSearch_Click() If Len(cboSearchField) = 0 Or...

Here it is with sample data. The SearchF form is activated from the Search command button on PersonalMasterF, which at the moment will not go into form view because of the error condition.