Search results

For some reason the web form is putting a space between the c and e at the end of the statement above. Instead of "Distanc e;" It should read "Distance;"

We'll get there :) try this: SELECT Member.Forename, Member.Surname, Event.Gender AS Gender, Event.Age AS Age, Event.Distance AS Distance, Event.Stroke AS Stroke, Venue.[Name of Pool] AS VenueName, Times.[Date] AS DateofEvent, Times.Time AS TimeRecorded FROM Member, Event, Times, Venue WHERE...

Hopefully this will crack the problem fingers crossed: SELECT Member.Forename, Member.Surname, First(Event.Gender) AS Gender, First(Event.Age) AS Age, First(Event.Distance) AS Distance, First(Event.Stroke) AS Stroke, Venue.[Name of Pool] AS VenueName, Times.[Date] AS DateofEvent, Times.Time AS...

Oh yes, a mistake on my part. I'm gonna have to think about this a bit more. I'll get back to you asap.

OK, I think I made one error, try this: SELECT Member.Forename, Member.Surname, First(Event.Gender) AS Gender, First(Event.Age) AS Age, First(Event.Distance) AS Distance, First(Event.Stroke) AS Stroke, Venue.[Name of Pool] AS VenueName, First(Times.Date) AS DateofEvent, Min(Times.Time) AS...

Try this: SELECT Member.Forename, Member.Surname, First(Event.Gender) AS Gender, First(Event.Age) AS Age, First(Event.Distance) AS Distance, First(Event.Stroke) AS Stroke, Venue.[Name of Pool] AS VenueName, First(Times.Date) AS DateofEvent, Min(Times.Time) AS TimeRecorded FROM Member, Event...

No problem. The blank dates you were getting might not have actually been Nulls, but empty strings instead. For example if a user enters a record of sale then decides to clear the date field and leave the record half completed the record will contain an empty string instead of a Null in the...

Try creating a new query and using the following SQL statement (just copy and paste into the SQL window) SELECT Member.Forename, Member.Surname, First(Event.Gender) AS Gender, First(Event.Age) AS Age, First(Event.Distance) AS Distance, First(Event.Stroke) AS Stroke, Venue.[Name of Pool] As...

OK I understand now, give me a little while to figure this out.

Are you sure that is the correct SQL statement? According to that both the Age and Gender fields are in the Event table which seems odd to me. Can you confirm.

Sure. Create a report based on the Result query(for example named ResultReport) and then add this line at the bottom of the code: Docmd.OpenReport "ResultReport", acViewPreview

Strange... When you create the new query don't add any tables or nothing. Click on SQL to open the SQL window and make sure when you paste there is only one ";" at the end of the statement. I have modified the statement to handle potential problems with Nulls(fields that contain nothing)...

In the query edit window there is a button on the upper left that you can click to run the query or you can select SQL and this gives you the SQL(Structered Query Language) statement.

SELECT RECORDID,IIf([salesdate1] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate1]) As Saledate FROM Table3 UNION SELECT RECORDID,IIf([salesdate2] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate2]) As Saledate FROM Table3...

I'll have a look at this tomorrow, in the meantime if anyone else cares to tackle it feel free, I'm pretty sure it's possible what you're trying to do though.

Would removing the If statement not solve this? (I may have misunderstood you here).

You need to remove the RecDate from the select statement. Just have Category, Type and Sum(I called it total).

SELECT DISTINCTROW Table1.Category, Table1.Type, Sum(Table1.Total) AS [Sum Of Total] FROM Table1 GROUP BY Table1.Category, Table1.Type; Hope this helps.

Could you paste your current select statement then I might be able to help from there.

The query "Result" will hold the latest conditions, so you could add this line to the bottom of the code: DoCmd.OpenQuery ("Result") Actually, you might find this better: DoCmd.OpenQuery "Result", acViewPivotTable, acReadOnly