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

[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.

 

[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;

 

[brown_eyes]

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

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;

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.