CheckBox Function In Queries

[ccepaulb]

I'm looking for someone that knows how to link check boxes on a form to the "show" check-box in a query. Basically, all I'm trying to do is have the end-user tell the query which fields to show in the output. If checkbox "A" is checked on a form prior to kicking off the query, I'd like field "A" to show in the query output...and so on.

Thanks, Paul

 

[namliam]

You cannot do it that way, you will have to create addaptive SQL using VBA...

SQL = "SELECT "
if me.showID = true then FIELDS = FIELDS & ", ID "
if me.showName = true then FIELDS = FIELDS & ", Name "
etc...
Fields = mid(fields,2) ' remove the first comma....
From = "From yourtable where .... etc..."

currentdb.querydefs("YourQueryName").SQL = sql & Fields & from

'Now run your query....

Hope it isnt as clear as mud and atleast understandable....

 

[ccepaulb]

Here's the SQL of the Query

Could someone help me figure out how I'd do that based upon this query?

Thanks, Paul

SELECT DISTINCTROW [CORE TABLE].CATEGORY, [CORE TABLE].SEGMENT, [CORE TABLE].[SUB SEGMENT], [CORE TABLE].CORE_UPC, [CORE TABLE].CORE_ITEM, Sum(ACNDATA.ACNUNITVOLCUR) AS [Sum Of ACNUNITVOLCUR], Sum(ACNDATA.ACNUNITVOLPRIOR) AS [Sum Of ACNUNITVOLPRIOR], Format([Sum Of ACNUNITVOLCUR]-[Sum Of ACNUNITVOLPRIOR],"#,###") AS UNITACTCHG, Format([UNITACTCHG]/[Sum Of ACNUNITVOLPRIOR],"Percent") AS UNITPERCCHG, Sum(ACNDATA.ACNDOLVOLCURR) AS [Sum Of ACNDOLVOLCURR], Sum(ACNDATA.ACNDOLVOLPRIOR) AS [Sum Of ACNDOLVOLPRIOR], Format([Sum Of ACNDOLVOLCURR]-[Sum Of ACNDOLVOLPRIOR],"$#,###") AS DOLACTCHG, Format([DOLACTCHG]/[Sum Of ACNDOLVOLPRIOR],"Percent") AS DOLPERCCHG, [CORE TABLE].MANUFACTURER, [CORE TABLE].BRAND, [CORE TABLE].FORMAT, [CORE TABLE].TYPE, [CORE TABLE].FLAVOR, [CORE TABLE].UNIT_SIZE
FROM (ACNDATA INNER JOIN [ACN TABLE] ON ACNDATA.ACN_SDESC = [ACN TABLE].ACN_SDESC) INNER JOIN ([CORE TABLE] INNER JOIN [UPC TABLE] ON [CORE TABLE].CORE_UPC = [UPC TABLE].CORE_UPC) ON [ACN TABLE].ACN_UPC = [UPC TABLE].ACN_UPC
GROUP BY [CORE TABLE].CATEGORY, [CORE TABLE].SEGMENT, [CORE TABLE].[SUB SEGMENT], [CORE TABLE].CORE_UPC, [CORE TABLE].CORE_ITEM, [CORE TABLE].MANUFACTURER, [CORE TABLE].BRAND, [CORE TABLE].FORMAT, [CORE TABLE].TYPE, [CORE TABLE].FLAVOR, [CORE TABLE].UNIT_SIZE
HAVING (((Sum(ACNDATA.ACNUNITVOLCUR))>0) AND ((Sum(ACNDATA.ACNUNITVOLPRIOR))>0) AND ((Sum(ACNDATA.ACNDOLVOLCURR))>0) AND ((Sum(ACNDATA.ACNDOLVOLPRIOR))>0))
ORDER BY [CORE TABLE].CATEGORY, [CORE TABLE].CORE_ITEM;

 

[namliam]

Uch, oh come on...

SQLFIELDS=""

SQLFrom = "FROM (ACNDATA INNER JOIN [ACN TABLE] ON ACNDATA.ACN_SDESC = [ACN TABLE].ACN_SDESC) INNER JOIN ([CORE TABLE] INNER JOIN [UPC TABLE] ON [CORE TABLE].CORE_UPC = [UPC TABLE].CORE_UPC) ON [ACN TABLE].ACN_UPC = [UPC TABLE].ACN_UPC"

SQLGROUP = "GROUP BY [CORE TABLE].CATEGORY, [CORE TABLE].SEGMENT, [CORE TABLE].[SUB SEGMENT], [CORE TABLE].CORE_UPC, [CORE TABLE].CORE_ITEM, [CORE TABLE].MANUFACTURER, [CORE TABLE].BRAND, [CORE TABLE].FORMAT, [CORE TABLE].TYPE, [CORE TABLE].FLAVOR, [CORE TABLE].UNIT_SIZE"

SQLHAVING = "HAVING (((Sum(ACNDATA.ACNUNITVOLCUR))>0) AND ((Sum(ACNDATA.ACNUNITVOLPRIOR))>0) AND ((Sum(ACNDATA.ACNDOLVOLCURR))>0) AND ((Sum(ACNDATA.ACNDOLVOLPRIOR))>0))
ORDER BY [CORE TABLE].CATEGORY, [CORE TABLE].CORE_ITEM;"

Now implement the if structure as I proposed above....

Then

currentdb.querydefs("YourQueryName").SQL = "SELECT " & SQLFIELDS & SQLFROM & SQLGROUP & SQLHAVING

It really isnt as hard as you think....