Results 1 to 5 of 5


  1. #1
    Timothy Stone Guest


    I usually use ASP with SQL Server 7.0, however I am trying to do a small project and I need a little help on three things...<BR><BR>1) I need to do a select to get the date out in SQL I could do it this way instead of datepart...<BR><BR> select curdate = getdate() from tablename<BR><BR>However I can&#039t do this in Access, how can I do this statement in access? (what is the command that is like the getdate() command in SQL?<BR><BR>2) I want to insert a column using insert into, but this table has an autonumber field in it, so how do I do an insert like this<BR><BR>insert into tablename values (&#039firstname&#039, &#039lastname&#039, &#039address&#039, &#039city&#039, &#039state&#039, &#039zipcode&#039)<BR><BR> If I try to do this with an autonumber field, it will bomb because I need some sort of placeholder for the field, however in SQL the identity field works fine and does not need a placeholder. How can I have it autonumber the insert statement and still submit the above line. I have tried NULL, I have tried inserting a value and it will not work, any ideas?<BR><BR>3) How can I select out items in a variable, like the above "select curdate = getdate() from tablename"<BR><BR>Thanks for your help! :) :) :)<BR>

  2. #2
    Jason Miller Guest

    Default Microsoft Access has Issues, yes...

    1) ... curDate = #" & date() & "# ... -- This uses the local system date and passes it into Access. Access may also have a date() function, but I can&#039t back that up.<BR><BR>2) insert into ... (column1, column2, column4) values (...) -- This is the proper syntax for the insert into statement so that you&#039re not reliant upon the database to keep your columns in the order you think you left them in. It also makes it so that you don&#039t have to provide a value for every column, thus making the "default" feature really really nifty.<BR><BR>3) select (My Expression) as ColumnName from ... -- This is standard SQL syntax, used primarily for aggregate functions. For simple queries (&#062; &#060; = like) on a single table, you might be well advised to just use the "where" bit of the syntax: select columnName from ... where (My Expression)<BR><BR>HiH

  3. #3
    Timothy Stone Guest

    Default RE: Microsoft Access has Issues, yes...

    1) Is there a built in date() function like getdate() on SQL, I have tried to do a select with date() but it errors out...<BR><BR>2) Is there anyway I can do the insert the way I am doing it now, the program I have is dynamic and I would have to do nested loops to be able to pull in the columns and add them to the insert string to be able to execute it correctly. Instead I currently have the data as dynamic which adds to the insert string such as <BR><BR>insert into tablename values(&#039firstname&#039, &#039lastname&#039, &#039email&#039, &#039phone&#039,getdate())<BR><BR>3) I tried the select (My Expression) as ColumnName, but I am having issues, it works fine in SQL, but not in Microsoft Access.

  4. #4
    Jason Miller Guest

    Default RE: Microsoft Access has Issues, yes...

    1) I import the date from a scripting/COM language, so I can&#039t help any more there.<BR><BR>2) No. But you have confused me -- if you know what data you&#039re pulling in (as you displayed), then you know which columns the data is going to, don&#039t you?<BR><BR>3) I have yet to make this fail while feeding it valid SQL syntax. In the meantime, you might try the "where" portion of the query, as suggested.

  5. #5
    Timothy Stone Guest

    Default RE: Microsoft Access has Issues, yes...

    1) I am currently using "NOW(date)" command in asp, but thats not what I want, I want to use a simular getdate() function (like SQL) instead, in SQL Server you can use Dateparts or you can use getdate, I want to use getdate(), but in ACCESS.<BR><BR>2) I am taking data from HTML form elements and creating a string that makes an insert statement, I would have to read in the column names from the db and then do a nested loop. All I want to do is when inserting data into an ACCESS database, I want to do all columns except the Autonumber and make it work, right now, it will not work unless I manually generate a number. How do I do an insert using the SQL language to insert into the Access database, that will allow me me use Autonumber?<BR><BR>In SQL you can do an insert into command and it will do it when you have an autonumber(Identity field) without using a placeholder, it does not seem to work the same way using Access, I get an error if I try to exclude the autonumber field from the insert statement. Why is this?<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts