textbox and query

Hi
I have the code below that allows me to test the result of a query
The query is a crosstab and pivots on months
what i want to do is check if the field december is different to blank
and if so increment the textbox.
the problem is
1. if no value for december the field is not included in the result
2. assuming i can get over 1st problem how can i check if field
december is different to space?

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("monthly_totals_team")
Set rs = qd.OpenRecordset()

If Not rs.EOF <> 0 Then
Me.Cboyears = Me.Cboyears + 1
End If
On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing


thanks

kevin

re: textbox and query

"Kevin" <kevcar40@btinternet.com> wrote in message
news:9f8487f2.0502270230.7ffea570@posting.google.c om...[color=blue]
> Hi
> I have the code below that allows me to test the result of a query
> The query is a crosstab and pivots on months
> what i want to do is check if the field december is different to blank
> and if so increment the textbox.
> the problem is
> 1. if no value for december the field is not included in the result
> 2. assuming i can get over 1st problem how can i check if field
> december is different to space?
>
> Dim db As DAO.Database
> Dim qd As DAO.QueryDef
> Dim rs As DAO.Recordset
> Set db = CurrentDb
> Set qd = db.QueryDefs("monthly_totals_team")
> Set rs = qd.OpenRecordset()
>
> If Not rs.EOF <> 0 Then
> Me.Cboyears = Me.Cboyears + 1
> End If
> On Error Resume Next
> Set rs = Nothing
> Set qd = Nothing
> Set db = Nothing
>
>
> thanks
>
> kevin[/color]


We can't see the SQL for the query, so do you mean that "December" might, or
might not, be a column heading? If so, what row do you need to locate, or
is there only one?
In general, you could use the .FindFirst method of the recordset if there is
more than one row, then check for the .NoMatch condition to see if you have
located the correct row. Once you have the right row, you can evaluate
Len(Trim(Nz(rst.Fields("December"),""))) to see whether it is null, blank or
a space. Of course if the column December does not exist, you have to catch
the error 3265 Item not found in this collection.

re: textbox and query

Kevin,
If I do understand your question correctly, then I think you need to 'force' your columns
to appear in the resultset.
How? Just put the ColumnHeadings in the properties of your query.
In your sql it would look like:
Transform .... Select .... FROM .... GROUPBY .....PIVOT Month In("jan","feb",
......,"dec");

--
Hope this helps
Arno R


"Kevin" <kevcar40@btinternet.com> schreef in bericht
news:9f8487f2.0502270230.7ffea570@posting.google.c om...[color=blue]
> Hi
> I have the code below that allows me to test the result of a query
> The query is a crosstab and pivots on months
> what i want to do is check if the field december is different to blank
> and if so increment the textbox.
> the problem is
> 1. if no value for december the field is not included in the result
> 2. assuming i can get over 1st problem how can i check if field
> december is different to space?
>
> Dim db As DAO.Database
> Dim qd As DAO.QueryDef
> Dim rs As DAO.Recordset
> Set db = CurrentDb
> Set qd = db.QueryDefs("monthly_totals_team")
> Set rs = qd.OpenRecordset()
>
> If Not rs.EOF <> 0 Then
> Me.Cboyears = Me.Cboyears + 1
> End If
> On Error Resume Next
> Set rs = Nothing
> Set qd = Nothing
> Set db = Nothing
>
>
> thanks
>
> kevin[/color]