Search results

Hi all, I'm trying to use VBA to open a form with link criteria to column(5) in a form control but I get invalid function. (referring to "lngzEntrantID.Column" in the link criteria) Basically, I need to make sure that it doesn't equal either value in 2 variables I have filled up. My code is...

Hi all, I have an issue where I'm calculating awards for people, but one of them is worked out by the person with the most points in certain categories, but if they won in the previous 2 years, then it goes to the person with the next highest points etc. Now, each PERSON has their own unique...

Wow! That works! My new code is a single query: Dim strRHSBMSelect As String Dim strRHSBMWhere As String Dim strRHSBMSQL As String strRHSBMSelect = "SELECT TOP 1 tblShowEntrantClass.lngzShowID, " _...

Hi Pat, Sorry, no, there are lots of classes but different awards for different ranges of classes. The class field needed to be included, in order to have it as part of the WHERE clause to be classes 1 - 13. I found that I needed to do two queries to get it that far, so that the specifics were...

Hi Pat, Thank you for your response. Sadly I needed it to group by the Entrant as I then need to find which entrant has the highest total (another question regarding that will surely arise soon). I have found that to do this, I needed to do it in a staged approach: I had to make a new table...

Hi all, "you tried to execute a query that does not include the specified expression 'lngzShowID' as part of an aggregate function" I'm trying to do a totals / summation in SQL but I am getting the above error. My code is: Dim strRHSBMSelect As String Dim...

Done it - no need to answer.

Hi all, I have an issue (Probably created to my bad database infracstructure) but it's only a luxury thing I'd like to do anyway... This is something that is similar to the filter one combo box based on another one, but with a difference. (I think). I have a Form with the following controls...

Ooooh that looks nice. My VBA and Access Experience, as you may expect from the above, is limited. I'm not an access virgin and I understand normalisation etc having done a Computer Science degree with modules on Software Systems Analysis and Design (DFDs etc). However, I don't know how to...

Got it working... Private Sub lngzClassID_AfterUpdate() Dim strWHERE As String Select Case Me.lngzClassID.Column(4) Case Is = 1 strWHERE = "tblResults.lngzShowDate=" & Me.lngzShowID & " AND tblResults.lngzClassID=" & Me.lngzClassID If DCount("*"...

Something like this...? Private Sub lngzClassID_AfterUpdate() Select Case Me.lngzClassID.Column(4) Case Is = 1 Dim rs As New ADODB.Recordset Dim strSELECT As String Dim strWHERE As String Dim strSQL As String MsgBox "Making new...

Hi all, I need to sort out some coding to see if a record exists based on 2 fields... lngzShowID and lnzgClassID in one form, being equal to that of the form which is issuing the command. If it doesn't, then I will open a form with the command to add, but otherwise nothing will happen. I...

Yes, I can see what you've done there. Sorry, I knew I had failed to mention something. I've actually already structed it the other way around... I have it so that there is a table called "Results" and the fields are as follows idsResultID lngzShowDate lngzClassID lngzFirstPlace...

Hi all, I am making a database for a local flower show (run by my Father in Law). I have made quite a lot of progress and a lot of the functionality. However I am now at the point where I need to transform the results of 1 - 4th place into scores and tallying them up. Basically, 1st place = 5...

Ok, I *think* the problem was actually in the first query "qryEntrantPerson". I've just tried something and it seems to be working now. We shall see.

I tried that but it still returned all the results, so it's sadly not working. Maybe I need to explain further. Ok, so I have a table called tblEntrantLookup, which has simply got 1 field in called "lngzEntrantID", that is a lookup to a query as follows: SELECT...

I have a query which is simply 1 field, but that field contains 6 columns (obviously numbered 0 - 5). Now I want to use a where clause so that the 4th column (or rather, column 3) is equal to a particular criteria. What is the syntax for this? The SQL code from the standard query with NO...

Hi all, thank you for your replies. They are very helpful. Mr. B, your comments seem particularly favourable and probably the way I would want to work. I have to completely agree, but thank you for your reminder to keep my on the straight and narrow. The foreign key in the entrants table -...

Hi all, Been a while since I've last been on here, but I have a new project that I hope you may all be able to help with. I'm creating a new db for a Flower Show which happens on a yearly basis, which incorporates scoring for 1st, 2nd, 3rd, 4th, Highly Commended and Commended (where...

Hi All, In my contact entries for my database, there is a lookup for existing companies which allows us add another person and have them belong to the same company. I also have Address which is also a lookup to existing addresses, so the same can be said for that. Many people could live at one...