HELP !!!! I have a table that consists of 6 can I create a string that will......

I have a table called: ENG_DB
It has fields called:  quote_id

I'm trying to keep track of a quote number.

The quote number works like this:

QXXX = the quote number which is ascending, changes with every different quote (in order) (ex. Q001 through Q020,etc.)
Z = is the quote revision (which is always associated with its quote number)  (ex. Q001.1,Q001.2, does not equal Q002.1)
yy = the year the project is quoted
mm = the month the project is quotes
xx = the number of the project (there could be 4 projects for 1 quote)(ex. Q001.0-05.04-01.0, Q001.0-05.04-02.0) <---this is two projects
z = the revision of the project (same rules as the other instances)
...The dashes and decimals are just for saving the file or creating the folders representation purposes only (need to be included but not in database)...I think  :)

I need a string that would combine all this into a usable representation of the file or folder and have the ability to create the folder when needed.  

I'm using a SQL database with VB6 or VB.NET

Who is Participating?
Here is a string that will put the fields into a usuable format.  Is that what you are asking?

MyString = "Q" & Format(quote_id, "000") & "." & quote_rev & "-" & Format(quote_yr, "00") & "." & Format(quote_mon, "00") & "-" & Format(qdock_num, "00") & "." & qdock_rev
brian_leightyAuthor Commented:
ya but how would I write that string to a folder
brian_leightyAuthor Commented:
to reword that I mean create a folder with that strings info
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Julian HansenCommented:
You could use the CreateDirectory API - very simple

Public Declare Function CreateDirectory Lib "kernel32" Alias "CreateDirectoryA" (ByVal lpPathName As String, lpSecurityAttributes As SECURITY_ATTRIBUTES) As Long

CreateDirectory ( pathname )


There is a catch ....

For the path


You would need to test to see that the path c:\folder\subfolder1 exists before trying to create


This means you would have to loop through the string looking for the first '\' and then calling CreateDirectory (Left(path, posofbackslash ) ).
Then finding the location of the next '\' and calling CreateDirectory again
Repeat until the path is created

That should do it - if you need explicit code post back here
Julian HansenCommented:
You could also use the CreateFolder method of the FileSystemObject

set fso = CreateObject ("Scripting.FileSystemObject" )
fso.CreateFolder ( "C:\folder\subfolder1" )

But as with the CreateDirectory API call you would need to create the path progressively to ensure all parents of the resulting folder exist in the file system.
brian_leightyAuthor Commented:
how do I add the string to the createdirectory API :

Quote_num = "Q" & Format(quote_id, "000") & "." & quote_rev & "-" & Format(quote_yr, "00") & "." & Format(quote_mon, "00") & "-" & Format(qdock_num, "00") & "." & qdock_rev

Is this code using SQL commands

What database are you writing SQL against?
No this code is VB code.  It should work in both VB.NET as well as VB6.  

Do you want to create a directory for every entry in your database?   Or do you want to be able to run some SQL code to find, say, a particular qote, and create a directory for just that single quote?
Here is what I suggest.  In Visual Basic, create a form and put a ListBox on the form that lists all of the quotes in your database.  This SQL query should will list them in the format you want:

SELECT 'Q' & Format(quote_id,'000') & '.' & quote_rev & '-' & Format(quote_yr,'00') & '.' & Format(quote_mon,'00') & '-' & Format(qdock_num,'00') & '.' & qdock_rev AS Expr1

Then place a button on your form called Create Directory.  You will select the quote you want in the list, and then click the button to create the directory.

Do you need code to create the directory, and link the listbox to the database?
Julian HansenCommented:
>>  how do I add the string to the createdirectory API :

I am not sure what you are asking here ? Can you elaborate ?

The CreateDirectory API is a simple call

CreateDirectory ( pathvariable)

That's it

pathvariable needs to be a full path otherwise directory will be created in whatever the current working directory is.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.