Source language: Translate to:

SQL Command Question

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Postby smartmedia » Wed Jul 23, 2008 12:58 pm

Try to separate SUM into multiple querys.
One line of code for it's SUM.

dbpExecSQL "mytrip" "Select SUM(clothing) as CostSum1 from [tripname];" ""
dbpExecSQL "mytrip" "Select SUM(food) as CostSum2 from [tripname];" ""
dbpExecSQL "mytrip" "Select SUM(gifts) as CostSum3 from [tripname];" ""
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Wrangler » Wed Jul 23, 2008 1:04 pm

It just gave me the closed dataset error 3 times.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Wed Jul 23, 2008 1:34 pm

Wrangler:
The database (mytrip) shows connected, the table ([tripname]) exists. I tried replacing [tripname] with hard copy table name (Baker to Reno), and it still didn't work.
Your Table Name contains spaces ... assuming Table Names can contain spaces, try ...
Code: Select all
dbpExecSQL "mytrip" "Select SUM(clothing) as CostSum1, SUM(food) as CostSum2, SUM(gifts) as CostSum3 from [#34]Baker to Reno[#34]" "TripSum1"
... making sure to use the same case (upper/lower) as in the original name ... if this fails, first try some simple case of this command ... where all you are doing is filtering out records that meet a simple criteria ... that should tell whether spaces are allowed or not.

Also try it on a Table Name without spaces.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Wed Jul 23, 2008 2:11 pm

Eureka! 8) It finally worked.

[#34]'s don't work as with dbf.

So spaces in table names are definitely not allowed. However, underscores instead of spaces are. So I just need to do some string replacing before creating the table. The table name is used to display the current trip to the user, so I need also do another stringreplace when displaying it.

Boy Howdy, databases are handy things, but they sure are finicky and a pain in the hiney. Particularly Access.

But it was not an adventure with no end. I learned a lot from it. I thank you both for the help.

I'm guessing this should speed up the summing process considerably. I've got about 35 fields to sum, so I'll find out once I get it all coded.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby smartmedia » Wed Jul 23, 2008 2:58 pm

Wrangler

As Gaev said correctly, setting any names of tables or fields with spaces will end with problem both in MDB or MYSQL... If you use dates fields with mdb make sure you put # before var.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Previous

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest