Source language: Translate to:

enforce table creation

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

enforce table creation

Postby carlos torres » Fri Jun 05, 2015 7:48 am

Dear NB team

TIA

I`m working on an special agenda with daily appointments. I take some data from the table agenda some from the clients in order to create a temporay table tmpAgenda
This is the code what I`m using

Code: Select all
  dbpOpenTable "Fdent" "Agenda" ""
   ...///
     dbpExecSQL "fdent" "SELECT Agenda.nTime, Agenda.Subject, Agenda.NextDate, HCO.LastName, HCO.Name, HCO.Phone, HCO.CelPhone INTO tmpAgenda|FROM HCO INNER JOIN Agenda ON HCO.IdClient = Agenda.Client|WHERE (((Agenda.Nextdate)=#[nDate]#))|ORDER BY Agenda.nTime;|" ""
   
   dbpOpenTable "Fdent" "tmpAgenda" ""


When I test it on MS Access it works fine. I change [nDate] adding or substracting a day but when i made the sql consult again doesn't work. The tmpAgenda is empty. The question is, how you can I enforce the creation of a temporary table?

I will appreciate any help.

carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Re: enforce table creation

Postby Gaev » Fri Jun 05, 2015 9:15 am

carlos torres:

I am confused.

When I test it on MS Access it works fine.

What works fine ? ...

a) is the temporary Table created ?

b) does it contain the expected records ?

I change [nDate] adding or substracting a day but when i made the sql consult again doesn't work. The tmpAgenda is empty.

If the Table is empty, could it be because there were no records matching the (Agenda.Nextdate)=#[nDate]# condition ?

Perhaps you are not adding/subtracting a day correctly ... can you ...

a) insert this ...
Code: Select all
AlertBox "nDate" "[nDate]"
... just before the dbpExecSQL command ... and post your results (first time and after addition/subtraction0 here.

b) post the commands used to populate the [nDate] variable (both initially and to add/subtract a day).

enforce the creation of a temporary table

What do you mean by this ? ... did it not create this Table the first time ? ... if you want, you can ...
Code: Select all
dbpGetTableNames "Fdent" "!," "[TableList]"
SearchStr "tmpAgenda" "[TableList]" "[tmpAgendaPos]"
If "[tmpAgendaPos]]" ">" "0"
   dbpDropTable "Fdent" "tmpAgenda"
EndIf
... to delete any previous copy of the temporary Table.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: enforce table creation

Postby carlos torres » Fri Jun 05, 2015 9:48 am

Dear Gaev

Thanks for your quick answer!

This is how I populate [nDate]

Code: Select all
DateToNum "[Today]" "Default" "[Tomorrow]"
Math "[Tomorrow]+1" "0" "[Tomorrow]"
NumToDate "[Tomorrow]" "Default" "[nDate]"
alertbox "" "[Tomorrow]"
  ...///
... this routine works with [nDate]
GoSub "ShowAgenda"


The tmpAgenda table is created trought SQL statement but it doesn`t load the selected records. That's my question
How can I enforce the populations of the temporary table.

Thanks again Gaev!
regards.
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Re: enforce table creation

Postby Gaev » Fri Jun 05, 2015 10:25 am

carlos torres:

According to this website ... http://en.wikipedia.org/wiki/Date_and_t ... n_Colombia ...
In Colombia, the standard dd/mm/yyyy is widely used


Note that MS Access ONLY uses American date format (mm/dd/yyyy) ... so use this command ...
Code: Select all
NumToDate "[Tomorrow]" "m/d/yyyy" "[nDate]"


Also, I am not sure how you first populated [Today] ... so you may have to make sure that it is also in the format required by MS Access.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: enforce table creation

Postby carlos torres » Wed Jun 10, 2015 2:38 pm

Hi, Dear Gaev

TIA

The data I get on [Today] it's OK. For today I get "10/06/2015".
This is what I did in order to populate [today] var

Code: Select all
  StrParse "[DateShort]" "!/" "[DatePart]" "[count]"
  SetVar "[Today]" "![DatePart1]/[DatePart2]/[DatePart3]"


The problem is not the date. The real question is about the selection of the recordset to populate the temporary table tmpAgenda.

I tryed to do it through SQL statement, and apparently the command woks. I open the database with msaccess and the table tmpAgenda appears but it is empty by now I`m stuck, I dont know what to do to get a proper recordset (the records of the day)

I'll apperciate any help
regards, carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Re: enforce table creation

Postby carlos torres » Wed Jun 10, 2015 2:38 pm

Hi, Dear Gaev

TIA

The data I get on [Today] it's OK. For today I get "10/06/2015".
This is what I did in order to populate [today] var

Code: Select all
  StrParse "[DateShort]" "!/" "[DatePart]" "[count]"
  SetVar "[Today]" "![DatePart1]/[DatePart2]/[DatePart3]"


The problem is not the date. The real question is about the selection of the recordset to populate the temporary table tmpAgenda.

I tryed to do it through SQL statement, and apparently the command woks. I open the database with msaccess and the table tmpAgenda appears but it is empty by now I`m stuck, I dont know what to do to get a proper recordset (the records of the day)

I'll apperciate any help
regards, carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Re: enforce table creation

Postby carlos torres » Wed Jun 10, 2015 2:38 pm

Hi, Dear Gaev

TIA

The data I get on [Today] it's OK. For today I get "10/06/2015".
This is what I did in order to populate [today] var

Code: Select all
  StrParse "[DateShort]" "!/" "[DatePart]" "[count]"
  SetVar "[Today]" "![DatePart1]/[DatePart2]/[DatePart3]"


The problem is not the date. The real question is about the selection of the recordset to populate the temporary table tmpAgenda.

I tryed to do it through SQL statement, and apparently the command works fine. I open the database with msaccess and the table tmpAgenda appears but it is empty by now I`m stuck, I dont know what to do to get a proper recordset (the records of the day). Under msacces enviroment the query does what it has to do. I copy the statement and run it as a dbpExecSQL command and got nothing.

I'll apperciate any help
regards, carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Re: enforce table creation

Postby Gaev » Wed Jun 10, 2015 4:48 pm

carlos torres:

The data I get on [Today] it's OK. For today I get "10/06/2015".

Yes, but MS Access expects it to be 06/10/2015 (i.e. mm/dd/yy) ... so it considers 10/06/2015 to be October 6, 2015.

So, when you do ...
Code: Select all
DateToNum "[Today]" "Default" "[Tomorrow]"
Math "[Tomorrow]+1" "0" "[Tomorrow]"
NumToDate "[Tomorrow]" "Default" "[nDate]"

... [nDate] is 11/06/2015 ... and when you ask ...
Code: Select all
(Agenda.Nextdate)=#[nDate]#
... MS Access is going to return records for November 6, 2015 ... and if there are no records for that date, the tmpAgenda Table will be empty.

Note: I do not have MS Access (the product), but if it works for you, the front end must be converting your dd/mm/yyyy format to mm/dd/yyy ... and passing it to the backend (database engine) ... but when you are using NeoDBPro to connect directly with the backend, you MUST supply date strings in format mm/dd/yyyy !!!!
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: Bing [Bot] and 2 guests