Page 2 - Selecting Records

ASP Free Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

#16

May 8th, 2008, 03:21 PM

Jaykappy

Contributing User

Join Date: Feb 2005

Posts: 609

Time spent in forums: 4 Days 15 h 26 m 43 sec
Reputation Power: 22

I try this and still get "0" in the message box for a count

Thoughts?

This is what the string looks like int he message box

SELECT DISTINCT 'CASE_NO' FROM dbo_Core2 WHERE 'CASE_NO' = '00-10014'

Code:

' dimension variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stSQL As String
Dim lngRecCount As Integer

' create sql query
stSQL = "SELECT DISTINCT '" & varField & "' FROM dbo_Core2 "
' add criteria if necessary
stSQL = stSQL & "WHERE '" & varField & "' = '" & varAttribute & "'"
' open recordset from sql in snapshot mode
MsgBox stSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(stSQL, dbOpenSnapshot)
' if we are not at the end of the recordset/file
If Not rs.EOF Then
' then move to the last record
rs.MoveLast
End If
' get total or records in recordset
lngRecCount = rs.RecordCount
MsgBox lngRecCount
' close the recordset
rs.Close
' release memory
Set rs = Nothing
Set db = Nothing

#17

May 8th, 2008, 03:48 PM

sbenj69

Dark Sonic Apprentice :D

Join Date: Feb 2007

Location: Redneck part of Illinois

Posts: 1,296

Folding Points: 137454 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 2 Weeks 3 Days 1 h 46 m 58 sec
Reputation Power: 984

can you tell me what you get when you do this:

Code:

Dim varCount As Integer
varCount = DCount(varField, "dbo_Core2", "varField =" & varAttribute)

Msgbox varCount

Code:

Dim varCount As Integer
varCount = DCount(varField, "dbo_Core2", "varField ='" & varAttribute &'"')

Msgbox varCount

might want to play around with the quotes a bit if it's a string

__________________
Did I help you? If so gimme rep by clicking on the

at the top right corner of this post

Madness does not always howl. Sometimes, it is the quiet voice at the end of the day saying, "Hey, is there room in your head for one more?"

#18

May 8th, 2008, 03:53 PM

Jaykappy

Contributing User

Join Date: Feb 2005

Posts: 609

Time spent in forums: 4 Days 15 h 26 m 43 sec
Reputation Power: 22

The first one pops up a error

Run time error '20001':
You canceled the previous Operation

The Second one:
As Soon as I paste it the VarCount = ..... line turns red...
Seems like a syntax error....

#19

May 8th, 2008, 04:03 PM

sbenj69

Dark Sonic Apprentice :D

Join Date: Feb 2007

Location: Redneck part of Illinois

Posts: 1,296

Time spent in forums: 2 Weeks 3 Days 1 h 46 m 58 sec
Reputation Power: 984

is varattribute a string or number?

#20

May 8th, 2008, 04:04 PM

sbenj69

Dark Sonic Apprentice :D

Join Date: Feb 2007

Location: Redneck part of Illinois

Posts: 1,296

Time spent in forums: 2 Weeks 3 Days 1 h 46 m 58 sec
Reputation Power: 984

try this:

Code:

varCount = DCount(varField, "dbo_Core2", "varField = '" & varAttribute & '")

had an extra apostrophe in there

That's if it is a string..... if it is numeric, try this way:

Code:

varCount = DCount(varField, "dbo_Core2", "varField =" & varAttribute)

Last edited by sbenj69 : May 8th, 2008 at 04:08 PM.

#21

May 8th, 2008, 04:09 PM

Jaykappy

Contributing User

Join Date: Feb 2005

Posts: 609

Time spent in forums: 4 Days 15 h 26 m 43 sec
Reputation Power: 22

Its a string...what I am doing is grabbing the Field Name which is set to VarField
Then testing which cbo box has a value in it and assigning that value to varAttrbute

Then clear all values from the comboboxes...the comboboxes are the 4 comboboxes that I am storing the attributes in....

Code:

Private Sub Command110_Click()

Dim varField As String
varField = Selector1.Column(1)

Dim varAttribute1 As String
varAttribute1 = ""

If Not cbo1 = "" Then
    varAttribute1 = cbo1
ElseIf Not cbo2 = "" Then
    varAttribute1 = cbo2
ElseIf Not cbo3 = "" Then
    varAttribute1 = cbo3
ElseIf Not cbo4 = "" Then
    varAttribute1 = cbo4
Else
End If

Dim varAttribute As String
varAttribute = varAttribute1

Dim varCount As Integer
varCount = DCount(varField, "dbo_Core2", "varField =" & varAttribute)

Msgbox varCount

cbo1 = ""
cbo2 = ""
cbo3 = ""
cbo4 = ""
varAttribute1 = ""

End Sub

#22

May 8th, 2008, 04:15 PM

Jaykappy

Contributing User

Join Date: Feb 2005

Posts: 609

Time spent in forums: 4 Days 15 h 26 m 43 sec
Reputation Power: 22

First EXAMPLEI am gettign errors in your first one

varCount = DCount(varField, "dbo_Core2", "varField = '" & varAttribute & '")

I change the end to

"varField = '" & varAttribute & "'"

I can hover over:
varfield it shows "CASE_No"
"varfield = and it shows nothing
'" & varAttribute & "' and it shows "01-2666"

SECOND EXAMPLE

I get the run time error 2001
YOu canceled the Previous operation

IM LOST HERE

#23

May 8th, 2008, 04:16 PM

sbenj69

Dark Sonic Apprentice :D

Join Date: Feb 2007

Location: Redneck part of Illinois

Posts: 1,296

Time spent in forums: 2 Weeks 3 Days 1 h 46 m 58 sec
Reputation Power: 984

on your msgbox, include varattribute

msgbox varcount & varattribute

Also, use the first example in my previous post for the dcount

#24

May 8th, 2008, 04:18 PM

Jaykappy

Contributing User

Join Date: Feb 2005

Posts: 609

Time spent in forums: 4 Days 15 h 26 m 43 sec
Reputation Power: 22

If I hard code it to a know CASE_NO I still get 0 ZERO

'' STRING
Dim varCount As String
varCount = DCount("CASE_NO", "dbo_Core2", "CASE_NO" = "01-2666")
MsgBox varCount

#25

May 8th, 2008, 04:19 PM

Jaykappy

Contributing User

Join Date: Feb 2005

Posts: 609

Time spent in forums: 4 Days 15 h 26 m 43 sec
Reputation Power: 22

If I use this it turns red...think syntax

This is your first example a couple posts ago

varCount = DCount(varField, "dbo_Core2", "varField = '" & varAttribute & '")

#26

May 8th, 2008, 04:19 PM

sbenj69

Dark Sonic Apprentice :D

Join Date: Feb 2007

Location: Redneck part of Illinois

Posts: 1,296

Time spent in forums: 2 Weeks 3 Days 1 h 46 m 58 sec
Reputation Power: 984

Quote:

Originally Posted by Jaykappy

Ok, I see what you're doing, varfield changes..... so change the dcount to this:

Code:

varCount = DCount(varField, "dbo_Core2", varField & " = '" & varAttribute & '")

#27

May 8th, 2008, 04:20 PM

Jaykappy

Contributing User

Join Date: Feb 2005

Posts: 609

Time spent in forums: 4 Days 15 h 26 m 43 sec
Reputation Power: 22

I can get it to run if I do this

I spaced them out to see them

varAttribute & " ' ")

#28

May 8th, 2008, 04:22 PM

Jaykappy

Contributing User