Help with DCount

[weircolin]

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

Hi Weircolin

I am having my own dcount problems (see the post below) however from my
research I think your code should read:

If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database", "[Year
Born] >= '1988' And [Year Born] <= '2006'")
End If

 

[weircolin]

Hi Mollybot

Thanks for that, appreciate it.

I couldn't get it working when using VB but when I put the DCount
expression into the Control Source it does work, but means I can't get
it working with the If Statement.

Can anyone shed any light on this?

Thanks

Hi Weircolin

I am having my own dcount problems (see the post below) however from my
research I think your code should read:

If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database", "[Year
Born] >= '1988' And [Year Born] <= '2006'")
End If

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")

 

[weircolin]

Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin

Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Year(Date) returns the current year. The Year function returns the year
portion of a date.

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Good question. Rather than having a count for each year band, you could set
the value of a varialbe based on the year band then do the count based on
that.

Select Case Me.cboyeargroup
Case is = "Under 18"
IntLowAge = 0
IntHighAge = 18
Case is = "19 - 24"
intLowAge = 19
IntHighAge = 24
Case is = "25 - 40"
intLowAge = 25
IntHighAge = 40
End Select

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] Between " & intLowAge & " And " & intHighAge)
End If

Thanks so much

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

In which event are you executing the code? It should be in the After Update
event of the combo box.

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin

Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

In which event are you executing the code? It should be in the After Update
event of the combo box.

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin

Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

In which event are you executing the code? It should be in the After Update
event of the combo box.

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

Hi

I got an error message up earlier about the Year(Date) function I was
using. Is there a possiblity this isn't working on my machine?

I am using Windows 2000 Pro and Access 2k2.

Thanks

Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

:

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

The message says the following

The Object Doesn't contain the Automation object 'Date'

You tried to runa Visual Basic Proceedure to set a property or method
for an object, however, the component doesn't make the property or
method available for automation operations.

Check the component's documentation for information on the properties
and methods it make available for Automation operations.

My brain is really starting to hurt!

Colin

Hi

I got an error message up earlier about the Year(Date) function I was
using. Is there a possiblity this isn't working on my machine?

I am using Windows 2000 Pro and Access 2k2.

Thanks

Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

Colin
Klatuu wrote:
hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

:

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

This is usually a Library Reference problem. Open VBA editor, select Tools,
References and see if any are marked *Missing*

Also, this site may help resolve the issue
http://support.microsoft.com/default.aspx?scid=kb;en-us;283115

The message says the following

The Object Doesn't contain the Automation object 'Date'

You tried to runa Visual Basic Proceedure to set a property or method
for an object, however, the component doesn't make the property or
method available for automation operations.

Check the component's documentation for information on the properties
and methods it make available for Automation operations.

My brain is really starting to hurt!

Colin

Hi

I got an error message up earlier about the Year(Date) function I was
using. Is there a possiblity this isn't working on my machine?

I am using Windows 2000 Pro and Access 2k2.

Thanks

Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

:

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

Colin
Klatuu wrote:
hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

:

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

Hi

There's nothing there that I can see thats marked Missing. The ones
that are selected are

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library
OLE Automation.

Thanks

This is usually a Library Reference problem. Open VBA editor, select Tools,
References and see if any are marked *Missing*

Also, this site may help resolve the issue
http://support.microsoft.com/default.aspx?scid=kb;en-us;283115

The message says the following

The Object Doesn't contain the Automation object 'Date'

You tried to runa Visual Basic Proceedure to set a property or method
for an object, however, the component doesn't make the property or
method available for automation operations.

Check the component's documentation for information on the properties
and methods it make available for Automation operations.

My brain is really starting to hurt!

Colin

Hi

I got an error message up earlier about the Year(Date) function I was
using. Is there a possiblity this isn't working on my machine?

I am using Windows 2000 Pro and Access 2k2.

Thanks

Colin
Klatuu wrote:
Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

:

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

Colin
Klatuu wrote:
hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

:

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

Well, something is missing. I don't know which library the date functions
are in. Try reading through the article on the link I sent. You may find
something there.

Here is another link that may help
http://allenbrowne.com/ser-38.html

Hi

There's nothing there that I can see thats marked Missing. The ones
that are selected are

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library
OLE Automation.

Thanks

This is usually a Library Reference problem. Open VBA editor, select Tools,
References and see if any are marked *Missing*

Also, this site may help resolve the issue
http://support.microsoft.com/default.aspx?scid=kb;en-us;283115

The message says the following

The Object Doesn't contain the Automation object 'Date'

You tried to runa Visual Basic Proceedure to set a property or method
for an object, however, the component doesn't make the property or
method available for automation operations.

Check the component's documentation for information on the properties
and methods it make available for Automation operations.

My brain is really starting to hurt!

Colin
(e-mail address removed) wrote:
Hi

I got an error message up earlier about the Year(Date) function I was
using. Is there a possiblity this isn't working on my machine?

I am using Windows 2000 Pro and Access 2k2.

Thanks

Colin
Klatuu wrote:
Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

:

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

Colin
Klatuu wrote:
hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

:

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[weircolin]

Hi

Been trying some different things. Trying Year(Now()) which is at
least showing up something in the box, granted it is #Error.

Thanks

Well, something is missing. I don't know which library the date functions
are in. Try reading through the article on the link I sent. You may find
something there.

Here is another link that may help
http://allenbrowne.com/ser-38.html

Hi

There's nothing there that I can see thats marked Missing. The ones
that are selected are

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library
OLE Automation.

Thanks

This is usually a Library Reference problem. Open VBA editor, select Tools,
References and see if any are marked *Missing*

Also, this site may help resolve the issue
http://support.microsoft.com/default.aspx?scid=kb;en-us;283115

:

The message says the following

The Object Doesn't contain the Automation object 'Date'

You tried to runa Visual Basic Proceedure to set a property or method
for an object, however, the component doesn't make the property or
method available for automation operations.

Check the component's documentation for information on the properties
and methods it make available for Automation operations.

My brain is really starting to hurt!

Colin
(e-mail address removed) wrote:
Hi

I got an error message up earlier about the Year(Date) function I was
using. Is there a possiblity this isn't working on my machine?

I am using Windows 2000 Pro and Access 2k2.

Thanks

Colin
Klatuu wrote:
Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

:

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

Colin
Klatuu wrote:
hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

:

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin

 

[Guest]

This is definitly a missing reference problem. I don't know which library
the date functions are in, but that is the one you have to find out and set
up.

Hi

Been trying some different things. Trying Year(Now()) which is at
least showing up something in the box, granted it is #Error.

Thanks

Well, something is missing. I don't know which library the date functions
are in. Try reading through the article on the link I sent. You may find
something there.

Here is another link that may help
http://allenbrowne.com/ser-38.html

Hi

There's nothing there that I can see thats marked Missing. The ones
that are selected are

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library
OLE Automation.

Thanks

Colin
Klatuu wrote:
This is usually a Library Reference problem. Open VBA editor, select Tools,
References and see if any are marked *Missing*

Also, this site may help resolve the issue
http://support.microsoft.com/default.aspx?scid=kb;en-us;283115

:

The message says the following

The Object Doesn't contain the Automation object 'Date'

You tried to runa Visual Basic Proceedure to set a property or method
for an object, however, the component doesn't make the property or
method available for automation operations.

Check the component's documentation for information on the properties
and methods it make available for Automation operations.

My brain is really starting to hurt!

Colin
(e-mail address removed) wrote:
Hi

I got an error message up earlier about the Year(Date) function I was
using. Is there a possiblity this isn't working on my machine?

I am using Windows 2000 Pro and Access 2k2.

Thanks

Colin
Klatuu wrote:
Not necessarily.
If you use the last code I sent where the 2 variables are set in the select
statement in the After Update event of the combo box, it should change when
you change the value in the combo.

:

I changed the criteria so it was for a different age group, but
nothing!

Like I said, if I put straight into the "Control Source" of the text
box if shows me the values, but obviously that stops me from doing the
If Then statement.

Colin
Klatuu wrote:
hhhhmmmmmm
Well, try checking your table to see if any records meet the criteria. I
tested it using a table in my database and got the correct count. Also, copy
the code into the immediate window so you can see what is being returned.

:

Hey

I was using BeforeUpdate, but have changed it to AfterUpdate and still
nothing is happening!

This is what I've got at the moment

Private Sub txttotal_AfterUpdate()

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " -
[Year Born] <= 18 ")
Else
txttotal = "Please Select"
End If
End Sub

Thinking is time for me to give up!!

Thanks

Colin
Klatuu wrote:
In which event are you executing the code? It should be in the After Update
event of the combo box.

:

Hi

I have tried both codes, no error message comes up, but the text box
remains blank when I make a selection.

Thanks

Colin
(e-mail address removed) wrote:
Hi Klatuu

Thanks for that, making sense now!

I see what you're saying about having to change the code every year. I
didn't think there was a way of doing it other than that! When you put
in Year(Date), what would I put in there? Or is that referencing the
current date?

Also, what code would I use to get it to show me how many people are in
the 25-40 age band?

Thanks so much

Colin
Klatuu wrote:
If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
"[Year Born] >= 1998 And [Year Born] <= 2006")

You don't need the WHERE in any Domain Aggragate functions; however, the
WHERE argument does conform to Jet SQL rules.

One other thing. Note I added the Me. to cboyeargroup. Not qualifying your
object references leads to confusion. Also, I would consider changing the
above code so that the years are not hard coded. This means every year,
someone has to remember to go back into the code and change it so it works
correctly. As it is, you are check for someone under 8, not 18. I would
suggest this alternative:

If Me.cboyeargroup = "Under 18" Then
txttotal = DCount("*", "Newsletter Database", Year(Date) & " - [Year
Born] <= 18 ")



:

Hi

I am inputing the following code and it doesn't seem to be working, it
shows up #Error?

Private Sub txttotal_BeforeUpdate(Cancel As Integer)
If cboyeargroup = "Under 18" Then
txttotal = DCount("[Reference Number]", "Newsletter Database",
WHERE([Year Born] >= 1998 And [Year Born] <= 2006))
End If
End Sub

Does anyone have any suggestions?

Cheers

Colin