Solved: Access 97 - Problem with SQL Statement.

[phendrena]

Hello again,

I have the following code :-
sSQL = "SELECT [7-Day Support (Total Actioned ~ Current Date)].[Total Of ID], [7-Day Support (Total Actioned ~ Current Date)].[Yes], [7-Day Support (Total Actioned ~ Current Date)].[No] " & _
"FROM [7-Day Support (Total Actioned ~ Current Date)];"
This code is part of an Excel userform that gets data from Access.
The problem that I have is that it is based on a crosstab query that isn't going to contain the values of 'Yes' or 'No' all the time. As such the code will error when the userform code is run.

Would anyone be able to suggest an alternative that won't cause any problems.

If it helps here is the SQL Crosstab query code :-

TRANSFORM Count(Inbound_Call_Log.ID) AS [The Value]
SELECT Inbound_Call_Log.DateOfCall, Inbound_Call_Log.[7DayTeam], Count(Inbound_Call_Log.ID) AS [Total Of ID]
FROM Inbound_Call_Log
WHERE (((Inbound_Call_Log.[7DayTeam])<>"" And (Inbound_Call_Log.[7DayTeam]) Is Not Null) AND ((Inbound_Call_Log.DateOfCall)=Date()))
GROUP BY Inbound_Call_Log.DateOfCall, Inbound_Call_Log.[7DayTeam]
PIVOT [Inbound_Call_Log].[7DayTeamActioned];

I have posted here as I feel this more of an Access query than an Excel query.

Thanks,

[CreganTur]

What error are you getting?

Are 'Yes' and 'No' values of a Yes/No field, or are they something else?

[phendrena]

What error are you getting?

Are 'Yes' and 'No' values of a Yes/No field, or are they something else?

Hi,

The error i'm getting is the following :
The Jet database doesn't recognise xxx as valid field name or expression.
I would expect this error as the data i'm pulling into excel is based on a crosstab query - this will of course only have the colums Yes and No when there is actually some data in the master table it's based on.

As for the values Yes/No they aren't part of a Yes/No tick box or combo box field. They are actual text values of Yes and No. There are two reasons for this :
1) The data is sent into access from an excel userform via ADO,
2) I'm not sure how to setup my Excel userform to send the data into the access database with a Yes/No field (i get a type mismatch error if i change the field to a Yes/No one).

Any suggestions?

Thanks,

[OBP]

In the Access Crosstab query are the Yes and No Column Headings?
If so make them permenant by putting them in the Query's Clomun Property "Column Headings"

[phendrena]

In the Access Crosstab query are the Yes and No Column Headings?
If so make them permenant by putting them in the Query's Clomun Property "Column Headings"
Hi OBP,

Thanks for the reply.
That is correct Yes & No are column headings in the crosstab.
I've now made them permanent (I didn't know you could that!) and it all works fine.

Thanks,