Anyone here leet at Access databases, I'm talking about dynamic queries/searches across multiple fields level.

Mainly so I can pick someones brain for information, solutions to problems I get stuck at.

Casp

_________________

I used to teach it and do some VBA stuff but have got a bit out of practice the last 2-3 years, might be able to help..

_________________

I might be able to help, what's the problem?

At the moment I basicaly struggling with how to make a query that searchs how many records there are for each month in the current year and the current year.

Also making a multiple field query, similar to Googles advanced search but with five fields.

Anyway I'll be in touch.

Thanks.

_________________

Don't know the names of your tables or columns but it's not too difficult if you create your query in SQL view, I've done it in two queries for clarity:

SELECT count(*) FROM Table WHERE year(Date) = currentYear;

This will return the number of records there are for a given Year where 'Table' is the name of your table, 'date' is the name of your date column and 'currentYear' is the year you want to select (leave the lowercase year part as that is part of the query). You may need to include currentYear in ' ' depending on how the data is stored.

SELECT count(*) AS "No. Records", month(Date) AS "Month" FROM Table GROUP BY month(Date)

Again, fill in the names of your columns where appropriate, leaving the lowercase month in as it's part of the query.


This should work, but without having the DB right in front of me I can't really tell. If you run into any problems, let me know.

This is the actual database, http://casp.gamecommunity.co.uk/database.gif I'm just working on the reports at the moment. Its just sussing out how you create sql quiries on the fly.

Thanks for your help anyway.

_________________

Updated my post with the second query, dunno why I thought it was gonna be trickier!

The month query I included will return every month at once, if you want to seperate it you can just add a where clause to the end and change the number for each month.

SELECT count(*) AS "No. Records", month(Date) AS "Month" FROM Table WHERE month(Date) = 1



EDIT: Just noticed your post, that's a bit different to what I was thinking, mine was more back-end query it yourself kinda thing. It's probably easier from your point of view to use the Access query designer as I don't know how much manually inputted SQL Access can take.

If I was doing it through a SQL query I'd be declaring parameters for the WHERE clause and having the search fields input into the params. Like I said, might be easier through an Access design tool. Need to ask someone else about those though, sorry!

Well , with help from http://www.access-programmers.co.uk/index.html I sussed the first one out, the second is proving a little more challenging.



This is how to do one with preset criteria but how you integrate it into the form and pull the criteria from the text boxes I dont know.

_________________

Again, this is more like SQL than Access:

-- Declare your variables
DECLARE beginDate as date
DECLARE endDate as date
DECLARE keyword as varchar(50)

-- pass the values from your fields into your variables
SET beginDate = dateField1
SET endDate = dateField2
SET keyword = keywordField

--Select your data
SELECT tblMain.ID1, tblMain.Dt, tblMain.Dt, tblMain.Coverage
FROM tblMain
WHERE tblMain.Keyword LIKE ''' + *keyword* + ''' AND tblMain.dt BETWEEN beginDate AND endDate



That won't actually work, but it's a start to show how you can pass your data straight into a piece of dynamic SQL. Tbh, you can probably pass your fields straight into the query without bothering about declaring variables. It just tends to be more extensible this way, especially if you are running multiple different types of query. Then you can just alter the variables rather than have to alter a potentially complex query.

EDIT: Btw, bit tired at the mo so that may be completely awful syntax...

Well I got everything working. This is the 'formulae' to filter the 5 fields.



The only thing I'm now having problems now with is the dates, I've tried 'between' and '>= or <=' but neither of them seem to be working and I'm not sure why.

_________________

are a pain , can you change them to a epoch so it miles easer ?

_________________

Totally. I've found out what the problem is, Access likes to work its dates as mm/dd/yyyy even though it displays them as dd/mm/yyyy. Just need to work out how to sort it out.

_________________

Why not just use SQL server...?

_________________

The Pancreas of S.T.F.U. | Never take life too seriously - nobody gets out alive anyway.
Disco_jim: um..... I have no excuse. | Chips: Thank the Beef | Rev Dr: Beef, I think i wee'd a little