Loading ...
Sorry, an error occurred while loading the content.
 

Re: Dates with Access

Expand Messages
  • biddy420z
    ... from ... 04 ... extract ... doesn t ... 00:00:00 , ... pound ... I ve ... CustomersID ; ... ($intCID) ; ... Access ... (thanx to another newsgroup, I have
    Message 1 of 5 , Apr 22, 2002
      --- In perl-beginner@y..., "biddy420z" <agatewood@n...> wrote:
      > --- In perl-beginner@y..., "b_harnish" <bharnish@t...> wrote:
      > > --- In perl-beginner@y..., "biddy420z" <agatewood@n...> wrote:
      > > > I have data in an Access database, and one of the fields is
      > Date/Time
      > > > with format 99/99/00;0 (the zero at the end means that the
      > slashes
      > > > are stored with the numbers).
      > > > When I query the database on the server using Perl "select *
      from
      > > > CallLog where Date like '6/4/01'", I get the results "2001-06-
      04
      > > > 00:00:00". This is fine, I really don't care since I can
      extract
      > what
      > > > I need from that string. However, when I try to insert a row
      > > > using "insert into CallLog (Date) values ('6/4/01')", it
      doesn't
      > > > work. I've tried several ways, '06/04/01', '2001-06-04
      00:00:00',
      > > > etc. and nothing seems to work. FYI, I have no trouble with any
      > other
      > > > fields. Any suggestions?
      > >
      > > Access requires single quotes (apostrophy's) around text, and
      pound
      > signs (hash marks, #) around dates.
      > >
      > > So, the insert statement would be:
      > > insert into CallLog (Date) values (#6/4/01#);
      > >
      > > - Brian
      >
      > Okay, I've got this answer from other discussion groups as well.
      I've
      > shortened the code,
      > only inserting the required field "CustomersID". This works. Then I
      > have the same query,
      > adding the field "Date". This doesn't work. Please tell me where I
      > went wrong?
      >
      > -Amy
      >
      > ############################
      > #!C:\Perl\bin\perl.exe -wT
      > use Win32::ODBC;
      > use CGI ':standard';
      >
      > my $intCID;
      >
      > my $strQuery = "select CustomersID from CallLog order by
      CustomersID";
      >
      > $strDSN = "CCC";
      >
      > print header;
      >
      > if (!($Data = new Win32::ODBC($strDSN))) {
      > print "Error connecting to $strDSN\n";
      > print "Error: " . Win32::ODBC::Error() . "\n";
      > exit;
      > }
      >
      > if ($Data->Sql($strQuery)) {
      > print "SQL failed.\n";
      > print "Error: " . $Data->Error() . "\n";
      > $Data->Close();
      > exit;
      > }
      >
      > while ($Data->FetchRow()) {
      > %Data = $Data->DataHash();
      > $intCID = $Data{CustomersID};
      > }
      >
      > $intCID = $intCID + 1;
      >
      > $Data->Close();
      >
      > my $strQuery1 = "insert into CallLog (CustomersID) values
      ($intCID)";
      > #my $strQuery1 = "insert into CallLog (CustomersID, Date) values
      > ($intCID, #6/4/01#)";
      >
      > if (!($Data = new Win32::ODBC($strDSN))) {
      > print "Error connecting to $strDSN\n";
      > print "Error: " . Win32::ODBC::Error() . "\n";
      > exit;
      > }
      >
      > if ($Data->Sql($strQuery1)) {
      > print "SQL failed.\n";
      > print "Error: " . $Data->Error() . "\n";
      > $Data->Close();
      > exit;
      > }
      >
      > print "Record saved successfully.";
      >
      > print "<br>";
      > print $intCID;
      > print "<br>";
      > print $strQuery1;
      > print "<br>";
      >
      > print end_html;
      >
      > $Data->Close();
      > ###############################
      >
      > Using the first query1, I get these results:
      >
      > Record saved successfully.
      > 60
      > insert into CallLog (CustomersID) values (60)
      >
      > ################################
      >
      > Using the second query1, I get these results:
      >
      > SQL failed. Error: [-3502] [2] [0] "[Microsoft][ODBC Microsoft
      Access
      > Driver]
      > Syntax error in INSERT INTO statement."
      >
      > ########################################

      (thanx to another newsgroup, I have finally solved this irritating
      problem...figured I'd post the solution, even though I now feel like
      an idiot - it's always the little things, isn't it)

      I found out that the word "date" is a reserved word in Access (hit me
      over the head), and that I need to enclose the word "date" in
      brackets, as in:

      insert into CallLog (CustomersID, [Date]) values (99, #6/4/01#)

      -- where 99, of course, is the next highest number since
      CustomersID is type autonumber. So, not only did I not enclose my
      date in (#), I used a reserved word which meant no matter what I did
      to that stupid date string, it would've never worked. What kind of
      luck is that!?!

      -Amy
    Your message has been successfully submitted and would be delivered to recipients shortly.