enforce table creation

[carlos torres]

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

[Gaev]

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.

[carlos torres]

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.

[Gaev]

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.

[carlos torres]

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

[carlos torres]

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

[carlos torres]

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

[Gaev]

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 !!!!