PLEASE HELP!!!! with Date range and InStr function.

B

brown_eyes

I am running reports by using the query by form technique. I have two text
boxes for date range and it works well when I enter a year, but when I leave
it blank I want to get all records in my report. I can't figure it out. I
keep getting blank reports when I leave the text boxes blank. Here is my
SQL. Thanks!

SELECT DISTINCTROW [Applications and Acceptances].IPEDS_CD,
Sum([Applications and Acceptances].Num_Apps) AS [Sum Of Num_Apps],
Sum(Grads_Total.TOTAL) AS [Sum Of TOTAL], Min([Applications and
Acceptances].AMCASYR) AS MinOfAMCASYR, Max([Applications and
Acceptances].AMCASYR) AS MaxOfAMCASYR
FROM Grads_Total INNER JOIN [Applications and Acceptances] ON
(Grads_Total.AMCASYR = [Applications and Acceptances].AMCASYR) AND
(Grads_Total.IPEDS_CD = [Applications and Acceptances].IPEDS_CD)
WHERE ((([Applications and Acceptances].AMCASYR) Between [Forms]![main
menu]![filter6] And [Forms]![Main menu]![filter7])) OR ((([Forms]![Main
menu]![filter6]) Is Null))
GROUP BY [Applications and Acceptances].IPEDS_CD;


I am also trying to pull more than one report at a time using the InStr
function. It works in the query but I don't know how to get it to work
through the unbound form. Here is the SQL:

SELECT [Inst Base Data 1-19-06B2].IPEDS_CD
FROM [Inst Base Data 1-19-06B2]
GROUP BY [Inst Base Data 1-19-06B2].IPEDS_CD
HAVING (((InStr([Ipeds code separated by commas,Blank=All],[Ipeds_cd]))>0 Or
(InStr([Ipeds code separated by commas,Blank=All],[Ipeds_cd])) Is Null));


Thanks in advance.
 
K

KARL DEWEY

Try this --
SELECT DISTINCTROW [Applications and Acceptances].IPEDS_CD,
Sum([Applications and Acceptances].Num_Apps) AS [Sum Of Num_Apps],
Sum(Grads_Total.TOTAL) AS [Sum Of TOTAL], Min([Applications and
Acceptances].AMCASYR) AS MinOfAMCASYR, Max([Applications and
Acceptances].AMCASYR) AS MaxOfAMCASYR
FROM Grads_Total, [Applications and Acceptances]
WHERE ((([Applications and Acceptances].[AMCASYR]) Between [Forms]![main
menu]![filter6] And [Forms]![Main menu]![filter7])) OR ((([Applications and
Acceptances].[AMCASYR]) Like IIf([Forms]![Main menu]![filter6] Is
Null,"*",Null)))
GROUP BY [Applications and Acceptances].IPEDS_CD;
 
B

brown_eyes

Thanks for the fast response but it still does not work.

KARL DEWEY said:
Try this --
SELECT DISTINCTROW [Applications and Acceptances].IPEDS_CD,
Sum([Applications and Acceptances].Num_Apps) AS [Sum Of Num_Apps],
Sum(Grads_Total.TOTAL) AS [Sum Of TOTAL], Min([Applications and
Acceptances].AMCASYR) AS MinOfAMCASYR, Max([Applications and
Acceptances].AMCASYR) AS MaxOfAMCASYR
FROM Grads_Total, [Applications and Acceptances]
WHERE ((([Applications and Acceptances].[AMCASYR]) Between [Forms]![main
menu]![filter6] And [Forms]![Main menu]![filter7])) OR ((([Applications and
Acceptances].[AMCASYR]) Like IIf([Forms]![Main menu]![filter6] Is
Null,"*",Null)))
GROUP BY [Applications and Acceptances].IPEDS_CD;

brown_eyes said:
I am running reports by using the query by form technique. I have two text
boxes for date range and it works well when I enter a year, but when I leave
it blank I want to get all records in my report. I can't figure it out. I
keep getting blank reports when I leave the text boxes blank. Here is my
SQL. Thanks!

SELECT DISTINCTROW [Applications and Acceptances].IPEDS_CD,
Sum([Applications and Acceptances].Num_Apps) AS [Sum Of Num_Apps],
Sum(Grads_Total.TOTAL) AS [Sum Of TOTAL], Min([Applications and
Acceptances].AMCASYR) AS MinOfAMCASYR, Max([Applications and
Acceptances].AMCASYR) AS MaxOfAMCASYR
FROM Grads_Total INNER JOIN [Applications and Acceptances] ON
(Grads_Total.AMCASYR = [Applications and Acceptances].AMCASYR) AND
(Grads_Total.IPEDS_CD = [Applications and Acceptances].IPEDS_CD)
WHERE ((([Applications and Acceptances].AMCASYR) Between [Forms]![main
menu]![filter6] And [Forms]![Main menu]![filter7])) OR ((([Forms]![Main
menu]![filter6]) Is Null))
GROUP BY [Applications and Acceptances].IPEDS_CD;


I am also trying to pull more than one report at a time using the InStr
function. It works in the query but I don't know how to get it to work
through the unbound form. Here is the SQL:

SELECT [Inst Base Data 1-19-06B2].IPEDS_CD
FROM [Inst Base Data 1-19-06B2]
GROUP BY [Inst Base Data 1-19-06B2].IPEDS_CD
HAVING (((InStr([Ipeds code separated by commas,Blank=All],[Ipeds_cd]))>0 Or
(InStr([Ipeds code separated by commas,Blank=All],[Ipeds_cd])) Is Null));


Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top