How change value of field in access?

sec · Post by **sec** » Sat Jan 29, 2005 3:38 am

I had a table that has three fields (datestring, date1, date2), datestring had value as "1/1988-2/1999"...Now want to split it: date1 = '1/1988' and date2 = '2/1999' (MS Access and database library of pb)
Thank for your answer!

Beach · Post by **Beach** » Sat Jan 29, 2005 8:24 am

Here is how I would do this:

Code: Select all

string$ = "1/1988-2/1999"

date1$ = StringField(string$,1,"-")
date2$ = StringField(string$,2,"-")

Debug date1$ 
Debug date2$

Beach · Post by **Beach** » Sat Jan 29, 2005 8:31 am

Or maybe this way:

Code: Select all

string$ = "1/1988-2/1999"

date1$ = Left(string$,FindString(string$,"-",1)-1)
date2$ = Right(string$,Len(string$)-FindString(string$,"-",1))

Debug date1$
Debug date2$

The first example is cleaner.

sec · Post by **sec** » Sat Jan 29, 2005 12:16 pm

Beach, thank

My question is: how fill two fields date1 and date2 with datestring filled?

Paul · Post by **Paul** » Sat Jan 29, 2005 4:47 pm

Here is simple example (I use MDB_Lib for easy database connection)

Code: Select all

;This example uses my MDB_Lib for easy MSAccess database connection
;available at http://www.pureproject.net  (in the ASM libs section)
;Example by Paul Leischow - Jan.29 /2005


curdir.s=Space(260) 
GetCurrentDirectory_(260,@curdir) 
If Right(curdir,1)<>"\":curdir+"\":EndIf 


MDB_Create(curdir+"MyDB") ;create database in curent folder called MyDB.mdb 

hMDB=MDB_Connect(curdir,"MyDB","","") ;connect to database Test located in current folder 
If hMDB 

  ;This will create the table/fields in the database an fill it with data
  ;If the database already exists, this section will be skipped
  qry$="Create table myDates(id autoincrement,datestring text(50),date1 text(20),"
  qry$+"date2 text(20),constraint myDates unique(id));"
  If DatabaseQuery(qry$)
    For dat=1 To 4
      Read mydat$
      If DatabaseQuery("insert into myDates(datestring)values('"+mydat$+"');")
        ;data inserted ok
      EndIf
    Next
  EndIf
  
  
  ;This will get each row of the database returning the row id and the datestring
  ;Then it will parse datestring and update date1 and date2 of specified row
  If DatabaseQuery("Select myDates.id,myDates.datestring from myDates;")
    While NextDatabaseRow()
      id=GetDatabaseLong(0)
      mydat$=GetDatabaseString(1)
      date1$ = StringField(mydat$,1,"-")
      date2$ = StringField(mydat$,2,"-")
      DatabaseUpdate("Update myDates set date1='"+date1$+"',date2='"+date2$+"' where id="+Str(id)) 
    Wend
  EndIf
  
  
  ;Finally check to see new data in date1 and date2
  If DatabaseQuery("Select myDates.date1,myDates.date2 from myDates order by id asc;")
    While NextDatabaseRow()
      date1$=GetDatabaseString(0)
      date2$=GetDatabaseString(1)
      Debug date1$+" ... "+date2$
    Wend
  EndIf
  
  
  CloseDatabase(hMDB)
EndIf 
MDB_Disconnect("MyDB")
End
 


DataSection
  Data.s "1/1988-2/1999","5/1990-5/2001","3/2000-7/2005","8/1990-2/1997"
EndDataSection

sec · Post by **sec** » Sun Jan 30, 2005 10:05 am

Thank you and your library, Paul.
When open database created by above code, MS Access will understand data1 and date2 as fields has type of date? Because I will do some task as count date in date1 field to current date, month, year.
And too looking a document in .chm format or pdf for sql lang
Thank you.

Kanati2 · Post by **Kanati2** » Thu Jun 30, 2005 11:32 pm

Maybe you can help me while you are at it... I'm using your library and have the following code...

Code: Select all

  MDB_Create(curdir+"SMP")
  hMDB=MDB_Connect(curdir,"SMP","","")
  
  If hMDB
    qry$ = "CREATE TABLE Prefs([id] autoincrement, [GUIX1] long, [GUIX2] long, [GUIY1] long, [GUIY2] long,"
    qry$ + "[VIDX1] long, [VIDX2] long, [VIDY1] long, [VIDY2] long, [subtitles] long, [aspectratio] long);"
    
    rslt = DatabaseQuery(qry)
    
    CloseDatabase(hMDB)
    MDB_Disconnect("SMP")

I get the database created correctly, but it doesn't create the table... It LOOKS like it should be fine, but I don't see why it doesn't work. I've tried with and without the semicolon at the end btw...

Paul · Post by **Paul** » Fri Jul 01, 2005 2:17 am

Kanati2 wrote: I get the database created correctly, but it doesn't create the table... It LOOKS like it should be fine, but I don't see why it doesn't work. I've tried with and without the semicolon at the end btw...

Maybe it's because your query is defines in qry$ and you send the query using qry... which contains nothing?

Also not sure why you use [ and ]. You can simply use the following...

Code: Select all

curdir.s=Space(260)
GetCurrentDirectory_(260,@curdir)
If Right(curdir,1)<>"":curdir+"":EndIf


MDB_Create(curdir+"SMP")
hMDB=MDB_Connect(curdir,"SMP","","")

If hMDB
  qry$ = "CREATE TABLE Prefs(id autoincrement, GUIX1 long, GUIX2 long, GUIY1 long, GUIY2 long,"
  qry$ + "VIDX1 long, VIDX2 long, VIDY1 long, VIDY2 long, subtitles long, aspectratio long);"

  If DatabaseQuery(qry$)
    ;ok
    Else
    Debug DatabaseError()
  EndIf
  

  CloseDatabase(hMDB)
EndIf
MDB_Disconnect("SMP")

Notice the check for database query errors. This will help when trying to find a problem.

Run this code once and the database containing your table and columns will be created. Run it again and it will return database error saying table already exists. This means everything works fine.

Kanati2 · Post by **Kanati2** » Fri Jul 01, 2005 5:35 pm

I found it... stupid mistake. I encapsulated the getcurrentdir_ call into a procedure and forgot to actually pass it back out of the procedure. So the MDB_Connect command never connected... THUS hMDB never got set and I never even dropped into the query.

Smack me a few times. I deserve it.

Also not sure why you use [ and ]. You can simply use the following...

Old SQL habits die hard. The brackets stop SQL reserved keywords from causing errors when you create a table with columns named the same. Just habit.