2

I'm trying to import the column names from an MS Access Table (named Learners) to some variable (not sure which one to use). The column's names are dates. When btnSave (TButton) is clicked the code should determine if the column with the current date has been created yet. If not then it should create it. This is the code I got so far:

procedure TForm1.btnSaveClick(Sender: TObject);  
var  
  bFound: boolean;  
  K: integer;  
  strColumnNames : TStringList;  
begin  
  strColumnNames := TStringList.Create;  
  tblLeerders.GetFieldNames(strColumnNames);  
  bFound := False;  

  for K := 1 to tblLeerders.IndexFieldCount  
    do  
      begin  
        if strColumnNames.Strings[K] <> FormatDateTime('dd/mm/yyyy', Date())  
          then  
            begin  
              bFound := True;  
            end;  
      end;  

  if bFound = False  
    then  
      begin  
        with qryLearners  
          do  
            begin  
              SQL.Text := 'Alter TABLE Leerders ADD COLUMN ' + FormatDateTime('dd/mm/yyyy', Date()) + ' Boolean ';  
              ExecSQL;  
            end;  
      end;  
end;

Please help! Any advice would be much appreciated.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
Armand Maree
  • 488
  • 2
  • 6
  • 21

1 Answers1

3

Try:

if Assigned(tblLeerders.FindField(FormatDateTime('dd/mm/yyyy', Date()))) then
begin
  qryLearners.SQL.Text := 'Alter TABLE Leerders ADD COLUMN ' + 
                           FormatDateTime('dd/mm/yyyy', Date()) + ' Boolean ';  
  qryLearners.ExecSQL; 
end;

(code edited as per Gerry, below)

You also said "Any advice would be appreciated". My advice is don't try to use a database like a spreadsheet. Store the data in normal fashion (in a table with a date & value column) and then denormalize for presentation. Access makes this easy to do with Crosstab Queries.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • 2
    That will cause an exception if the field doesn't exist. You need to use `if FindField(DateStr) <> nil` instead. – Gerry Coll Nov 16 '10 at 21:55
  • Thanks a lot! I'm grade 11... So yeah, I still need to learn a bit about what to use and what not (in terms of using the database as a spreadsheet). But thanks for the advice. :) – Armand Maree Nov 17 '10 at 19:16
  • Another thing. This program should say if a learner was absent or present that day. The learner's names is listed vertically and the date is listed horizontally. The query should then "Check" the Box in access under the date. What is the SQL I should use? Currently I'm using the following, but I get an Error Message saying Syntax Error in string in query expression '[Leerder Naam En Van] = "(#K': – Armand Maree Nov 17 '10 at 19:33
  • qryLearners.SQL.Text := 'UPDATE Leerders SET [' + FormatDateTime('dd/mm/yyyy', Date) + '] = True WHERE [Leerder Naam En Van] = "' + arrLeerders[K] + '"'; – Armand Maree Nov 17 '10 at 19:35