AnsweredAssumed Answered

Question about creating Query of multiple fields

Question asked by ChrisWelsh_1 on Oct 15, 2012
Latest reply on Oct 15, 2012 by philmodjunk


Question about creating Query of multiple fields


I am very happy that I have never needed to ask for help yet (not been using FMpro long) but I have a criteria that I need to fulfill and I cant figure out the best way to accomplish it and I was hoping some people here might have some ideas.

What I am making is a query of a database that stores the results of online classes that are made up of modules (up to ten modules each class) 

so when I import information into this database, there are cells that store a score for each module (m1s, m2s, m3s, etc...) and a cell that stores a timestamp (m1t, m2t, m3t, etc...) that denotes when a specific module score cell was filled using an If statement. (10 module score cells with ten cells with timestamps)

So I need to make a query that will search all ten of these dates cells and only show those records which received a score (thus a timestamp) during a certain time period.

I have a date search set with a button script that triggers the action using set fields and two date criteria to user puts in.

This works fine when I am trying to just search one date field but when I add additional set fields (my other date fields for the other 9 modules) then nothing comes up and I am pretty sure why this is is now going to only report records in which every module field conforms to the date requirement...which will almost never be the case so it shows nothing.

How would one go about, on one button push, creating a query of 10 different columns with cells for dates and bringing up ANY record that has any of the 10 cells on the rows with a date within the date search parameter? Instead of having it search all the cells and only bringing up records that have the time in all 10 records... the difference between any and all...


I hope I have made myself clear....Any help would be greatly appreciated...