Posts by boromuse

Re: Sql query 2 date values & 2 time

I will try and get it up laters as just got work
Thank you

Re: Sql query 2 date values & 2 time

So can anyone help me with this please?

Re: Sql query 2 date values & 2 time

The Datefrom & Dateto are from the table booking

Select
Booking.ID, Car.model,car.reg, Booking.datefrom, booking.timefrom, booking.dateto, booking.timeto 
FROM 
car inner join booking on car.ID = booking.car 
WHERE
Car.ID = 6 
And 
Booking.datefrom >= txtdatefrom 
And
Booking.dateto <= txtdateto
And
Booking.timefrom >= txttimefrom 
And
Booking.timeto <= txttimeto;

This is the is the one version i have got but cannot find the one that i used with the between clause and the text boxes are off the form
The query populates a list box in the form, hope this answers what you asked for

Ok i'm creating a list box that holds the results for an sql statement but i cannot get it right
what i'm trying to do is display record where the car is booked to say that it is unavailable

4x text boxes on a form
1x table called booking

txtdatefromvalue => booking.datefrom
txtdatetovalue <= booking.dateto

txttimefromvalue => booking.timefrom
txttimetovalue <= booking.timeto

i have tried to get it working but it does not work with a between value i have been told to concatinate the values but i dont no where to start so please can someone help me sort this issue out :confused:
Thank you

Re: format xl through access

Wow :rock:
Your ace at this!!!! thank you!

right this code is hit and miss works one second then comes up with a global error! running through access 97 exporting to excel 97

With wk.Sheets(strsheet)
        
    .Columns("a:z").EntireColumn.HorizontalAlignment = xlCenter
    .Range("A1:Z1").Font.Bold = True
    .Columns("A:Z").EntireColumn.AutoFit
    .Range("a1:h1").Select
    
This is the problem part!
#################################
    .Selection.Interior.ColorIndex = 37
    .Selection.Interior.Pattern = xlSolid
#################################


End With

Re: list of vb fomatting sheets

thats a good idea but i would like to know what other commands exist if possible but thank you

Can anyone point me in the direction for getting the commands for formatting excel sheets in vba??

Re: using replace in string value

Thats it people its now working after reformating the date to the us style
thank you everyone for the help

Re: using replace in string value

= #mm/dd/yyyy# but i think i have sorted this out bear with me a second

Re: using replace in string value

right what i have got is a form with a txtbox that has date format as "06/02/2006" so thats "dd/mm/yyyy"

then it creates the query

DoCmd.OpenQuery ("qryStaffListQuery")

this opens up and has the criteria as "mm/dd/yyyy" which is a complete pain!
cause i would have to reorder my date but saying that i have just thought i could just format the result to a hiden txtbox or string with the format of "mm/dd/yyyy" !!!!

Re: using replace in string value

ok this is annoying get another problem!!!

everything is running spot on but when i run there query after everything being set to the "dd/mm/yyyy" it opens the new query and the date is being set in the way of the US "mm/dd/yyyy" so WTF how do i stop this????

Re: using replace in string value

also the reason why im using format of "06/02/2006" is because im running a query where it saves the data that has the same date but then when saving the file i'm saving it as "06_02_2006" as the date format is not valid!

Re: using replace in string value

well it was if the user had put a different figure in but with the format it has just fixed and got rid of that so thank you!

Re: using replace in string value

Quote from norie

Could you not use Format?

Code

Dim stdText As String
stdText = "05/02/2006"
If IsDate(stdText) Then
    stdText = Format(stdText, "ddmmyyyy")
End If


MsgBox stdText

I had not actually thought of that
thank you for the idea

Re: using replace in string value

spot on thank you for that just trying to put it in a loop cause that single line is only running through once lol :rock:

Re: using replace in string value

i think its something to do with the libary reference!
its coming up with

compile error :
sub or function not defined

Re: access format xl

strsheet is the name of the page saved in excel but i have now changed that to have it as numerical and just use the file name as the date and everything is all cool now!
was me being stupid lol

right this worked fine at home but does not at work!!!!

Dim stdText As String
    stdText = Form_test.txtdate
    
    MyArray = Array("<", ">", "|", "/", "*", "\", "?", """")
    For x = LBound(MyArray) To UBound(MyArray)
        stdText = Replace(stdText, MyArray(x), "_", 1)
    Next x
    
    MsgBox stdText
    
    Form_test.txtdate = stdText

stdtext is the date format "05/02/2006" after this runs on my pc which is running office 2000 it converts it to "05_02_2006"

now at work we are running 97 which probably is the reason why it does not work! Please can anyone help me?

Re: access format xl

Quote from norie

Sorry I don't understand.:)

You can't save a workbook with that date format, it's an invalid filename.

Excel saves it as the date and instead of using the "/" it uses "_"
what i need to do is break the date in to the sections and it will work cause it just selected the sheet and ran through it!