Database

scorp_scorp14-Feb-11 0:07

14-Feb-11 0:07

The variables
scope and eid are in the code are the same as the one run in the access database.

I did one step, that i added one more record to my tables that meets the conditions in the sql statement and run both on database and in the recordset, the sql returns true result (expected i.e: 3 records) and the recordset returns only 1 single record. seems that number of records in the record set is stuck to 1 single record. Confused | :confused:

dont know why

And there is nothing special about this record, i mean its not the first record in the tables. its really weird D'Oh! | :doh:

0 will always beats the 1.

Wendelius14-Feb-11 8:43

14-Feb-11 8:43

Hi
When you say that the query returns only one record, have you looked at RecordCount -property? Also have you tried just using MoveNext method.

I ask this since depending on the cursor type the RecordSet object may not know how many records there are. For example if using ForwardOnly-cursor the total amount of records may be unknown until you've iterated through all of them.

The need to optimize rises from a bad design.My articles[^]

Corporal Agarn14-Feb-11 9:02

Corporal Agarn

14-Feb-11 9:02

I was taught to use MoveLast then MoveFirst to let the program know about all the records.

Wendelius14-Feb-11 9:05

14-Feb-11 9:05

Yes, that's one way of finding out the total amount of records, but not possible always. Consider a situation where your cursor is a forward only version. You can move to the end but you cannot go back since the result set is lost for those records that you have already passed.

The need to optimize rises from a bad design.My articles[^]

Corporal Agarn14-Feb-11 9:12

Corporal Agarn

14-Feb-11 9:12

You are correct. It was a long time ago when I learned that and the memory is not so good. Smile | :)

Wendelius14-Feb-11 9:26

Re: Recordset results (solved)

14-Feb-11 9:26

I hear you. Having the same problem and I'm staring to realize that the quality of my memory correlates negatively to the amount of gray hair, simply can't have both at the same time Smile | :)

The need to optimize rises from a bad design.My articles[^]

scorp_scorp14-Feb-11 16:09

Re: Recordset results (solved)

14-Feb-11 16:09

Thanks again Mika

I usually use movelast then move first after my recordset query ... seems i missed this time Sigh | :sigh:

, i used it and the recordcount brings true value (expected number of records).

Thanks agian Smile | :)

0 will always beats the 1.

Wendelius16-Feb-11 8:30

Syntex Error, Missing Operator !!!

16-Feb-11 8:30

No problem Smile | :)

The need to optimize rises from a bad design.My articles[^]

scorp_scorp12-Feb-11 17:47

12-Feb-11 17:47

Dear All, Am getting sytex error , Missing operator on the following:

sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In select c_number FROM tb_c_details WHERE e_id = " & eid

set rst = db.openrecordset (sqls)

Cant find any missing operator Unsure | :~

, also i used a msgbox to show sqls , the statement is perfect.

Helpppp !!! Frown | :(

0 will always beats the 1.

Re: Syntex Error, Missing Operator !!! DIDNT work [modified]

Luc Pattyn12-Feb-11 18:08

Luc Pattyn

12-Feb-11 18:08

scorp_scorp wrote:
Helpppp !!!

All examples I've seen have parentheses following the IN keyword...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

scorp_scorp12-Feb-11 18:53

12-Feb-11 18:53

Modified to:

sqls = "SELECT c_number, c_description, c_scope, c_category, c_type FROM tb_c WHERE c_scope = " & scope & " AND c_number In ( select c_number FROM tb_c_details WHERE e_id = " & eid & " )"

Didnt work ... same problem Cry | :((

Any different statment that would give me the same results ??? since i think the error is from the IN phrase .

0 will always beats the 1.

modified on Sunday, February 13, 2011 2:51 AM

Wendelius12-Feb-11 21:29

Re: Syntex Error, Missing Operator !!! [modified]

12-Feb-11 21:29

If you alreasy corrected the in clause, are the scope and eid variables containing character values? If theay are they should be between ''.

You could also post the contents of the sqls variable in order to see what's the actual statement that's executed against the database.

The need to optimize rises from a bad design.My articles[^]

scorp_scorp12-Feb-11 21:35

12-Feb-11 21:35

scope is a string and eid is a int.

dim scope as string
dim eid as integer
scope = CStr(Forms![report_testin]![Combo42].Value)
eid =Int(Forms![report_testin]![Combo29].Value)

ami defining the string sqls wrong???

I also tried to modify sqls to be:
sqls = "SELECT distinct c_number, c_description FROM tb_c INNER JOIN tb_c_details ON tb_c.c_number = tb_c_details.c_number WHERE e_id = " & eid

It generates another Error:
"The specifies Field 'c_number' could refer to more than one table listed in the FROM clause of your sql statement"

but it doesnt make sense for me WTF | :WTF:

0 will always beats the 1.

modified on Sunday, February 13, 2011 4:08 AM

Wendelius12-Feb-11 22:12

12-Feb-11 22:12

If scope is a string your statement should be something like

sqls = "SELECT c_number, c_description, c_scope, c_category, c_type " & _
FROM tb_c  " & _
WHERE c_scope = '" & scope & "' " & _
AND c_number IN ( SELECT c_number  " & _
                  FROM tb_c_details  " & _
                  WHERE e_id = " & eid & " )"

Also instead of using literals (concatenated values) in your statement you should use bind variables. This way you're more safe from SQL injections, datatype mismatches, syntax errors etc

[addition]
Concenring the latter error, you should use aliases in the select portion. Like:

SELECT distinct tb_c.c_number, tb_c.c_description ...

The need to optimize rises from a bad design.My articles[^]

scorp_scorp12-Feb-11 22:27

12-Feb-11 22:27

Wooooohoooooo Cool | :cool:

Thanks a lot Mika ,i appriciate , it worked perfect , and thanks for the Addidtional note too Wink | ;)

0 will always beats the 1.

Wendelius12-Feb-11 22:42

How to make auto number start from 2000

12-Feb-11 22:42

No problem Smile | :)

The need to optimize rises from a bad design.My articles[^]

ivo7511-Feb-11 7:18

ivo75

11-Feb-11 7:18

How to make auto number start from 2000 not in 1. I use MS Access 2003 database

Wendelius11-Feb-11 7:45

11-Feb-11 7:45

Hi,

Have you noticed for example this article: How to programmatically create an AutoNumber field and set its "New Values" property to Random[^]. Hopefully that gives you a good starting point.

However there are very rarely reasons why setting starting point would be meaningful. Typically the value itself is meaningless as long as it's unique.

The need to optimize rises from a bad design.My articles[^]

ivo7511-Feb-11 9:29

ivo75

11-Feb-11 9:29

I create my database in MS Access before use it.

Wendelius11-Feb-11 10:15

11-Feb-11 10:15

Yes, the article covers also database creation etc, but the point is that with a little macro you can modify the properties of your tables columns.

I don't have Access at the moment but I would guess that some macro like:

CurrentDb.TableDefs("TableNameGoesHere").Fields("FieldNameOverHere").DefaultValue = "2000"

would be worth trying.

The need to optimize rises from a bad design.My articles[^]

Problem installing SQL Server 2005 on Vista

Abhinav S12-Feb-11 21:47

Abhinav S

12-Feb-11 21:47

This[^] may help.

The funniest thing about this particular signature is that by the time you realise it doesn't say anything it's too late to stop reading it.

gaby5811-Feb-11 5:01

gaby58

11-Feb-11 5:01

Hi,

WhenI tried to install SQL Server 2005, it installs all the compnents except when it tries to install Database Services it was not able to start the service automatically and later it says it failed in installing Database Services, any information on this would be greatly appreciated.

Thanks

Re: Problem installing SQL Server 2005 on Vista

Wendelius11-Feb-11 5:32