runtime errror 3075

[Compufreak]

Hello everyone



just a stupid error i am getting with this....

strSQL = strSQL & " " & "WHERE [Clusters].Cluster_Desc =" & Chr(34) & [Combo1] & Chr(34) & " and " & "WHERE Source.Day_Month_Year Between #" & DMax("Day_Month_Year", "Source") & "# AND #" & DMin("Day_Month_Year", "Source") & "#" & ";"

can anyone tell me where i am going wrong ?

[June7]

Why the Chr(34)?

Don't repeat the WHERE keyword.

[Compufreak]

hi june

made the changes that you had told me to do.

strSQL = strSQL & " " & "WHERE [Clusters].Cluster_Desc =" & [Combo1] & " and " & "Source.Day_Month_Year Between #" & DMax("Day_Month_Year", "Source") & "# AND #" & DMin("Day_Month_Year", "Source") & "#" & ";"

but i am still getting the same error . i have attached a screenshot

[June7]

So Cluster_Desc is a text datatype? Need apostrophe delimiters, just as date criteria use # delimiter - numbers don't need any delimiter.

[Clusters].Cluster_Desc = '" & [Combo1] & "' and "


strSQL = strSQL & " WHERE [Clusters].Cluster_Desc='" & [Combo1] & "' AND Source.Day_Month_Year Between #" & DMax("Day_Month_Year", "Source") & "# AND #" & DMin("Day_Month_Year", "Source") & "#;"

[Compufreak]

thanks june seems to be working

just a tiny problem...

im using the code below to display the dates on the report

="Between"+Format([Forms]![ViewReportsForm]![StartDate],"\ d\ mmmm\ yyyy")+"  to  "+Format([Forms]![ViewReportsForm]![EndDate],"d\ mmmm\ yyyy")

i made sum changes to the code and ended up with this ...

="Between " & IIf(IsNull([StartDate]),DMin([Day_Month_Year),Format([Forms]![ViewReportsForm]![StartDate],"\ d\ mmmm\ yyyy")) & " to " & IIf(IsNull([EndDate]),DMax([Day_Month_Year]),Format([Forms]![ViewReportsForm]![EndDate],"\ d\ mmmm\ yyyy"))

but it does not seem to be working

[June7]

Good you changed to & for concatenation instead of +. Although + will usually work it is also addition operator and can get unexpected results.

The DMin and DMax functions syntax is wrong and missing the tablename.

="From " & IIf(IsNull([StartDate]),DMin("Day_Month_Year","Source"),Format([Forms]![ViewReportsForm]![StartDate],"\ d\ mmmm\ yyyy")) & " to " & IIf(IsNull([EndDate]),DMax("Day_Month_Year","Source"),Format([Forms]![ViewReportsForm]![EndDate],"\ d\ mmmm\ yyyy"))

Are [StartDate] and [EndDate] fields in the report RecordSource? Why would they be null?
="From " & Nz([StartDate],DMin("Day_Month_Year","Source")) & " to " & Nz([EndDate],DMax("Day_Month_Year","Source"))

[Compufreak]

i have made just a slight change to your solution there june.

instead of prompting to enter for a start date and end date i added the following .....


="From " & Nz([Forms]![ViewReportsForm]![StartDate],DMin("Day_Month_Year","Source")) & " to " & Nz([Forms]![ViewReportsForm]![EndDate],DMax("Day_Month_Year","Source"))

thanks for all your help june , i honestly and truly appreciate it.

just one last thing , i would like to add the following to that code just to make it display better. any idea where i can add it.
"\ d\ mmmm\ yyyy"

[June7]

You want to apply that format to the date values?

="From " & Format(Nz([Forms]![ViewReportsForm]![StartDate],DMin("Day_Month_Year","Source")),"\ d\ mmmm\ yyyy") & " to " & Format(Nz([Forms]![ViewReportsForm]![EndDate],DMax("Day_Month_Year","Source")),"\ d\ mmmm\ yyyy")

[Compufreak]

thanks so much june for all your help