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

Posted on 2005-04-25
Last Modified: 2010-04-17
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

Question by:brian_leighty
    LVL 2

    Accepted Solution

    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

    Author Comment

    ya but how would I write that string to a folder

    Author Comment

    to reword that I mean create a folder with that strings info
    LVL 50

    Assisted Solution

    by:Julian Hansen
    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
    LVL 50

    Expert Comment

    by:Julian Hansen
    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.

    Author Comment

    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

    LVL 2

    Expert Comment

    What database are you writing SQL against?
    LVL 2

    Expert Comment

    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?
    LVL 2

    Expert Comment

    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?
    LVL 50

    Expert Comment

    by:Julian Hansen
    >>  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.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    lucky13 challenge 11 96
    wait notify demo infinite loop 3 69
    java ^ examples 8 51
    python sqlite question 11 28
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    A short article about problems I had with the new location API and permissions in Marshmallow
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now