|
You decently cannot insult those who try to help.
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
Ah yes, the lazy person's fallback position when you don't get someone else to do your work: abuse.
|
|
|
|
|
It's the VB forum; it looks like a frustrated coder. Don't take it as a personal insult.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Eddy Vluggen wrote: Don't take it as a personal insult.
I don't; my shoulders are broad enough. But I do take exception to people being abusive on this site.
|
|
|
|
|
Richard MacCutchan wrote: But I do take exception to people being abusive on this site.
Good for me I never did that 
It is one of umpteen coders frustrated at how dates don't work like they imagine. Not something unique nor new.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
The trouble with some of these people is that they will not accept advice. So no matter how much you try to help them they insist on doing it their way, without understanding the potential problems that can come back to bite them.
|
|
|
|
|
Richard MacCutchan wrote: But I do take exception to people being abusive on this site.
Why is that, Thou sodden-witted lord?
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Weird since examples seem to work with a single comma.
You could try to escape extra characters (i.e., commas):
dtPick5.CustomFormat = "MM',' dd',' yyyy" or keep using a more common form.
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
I think the issue you may be facing is that you are storing the data as datetime which naturally includes the time component. If you do not need the time component then you should store it as a date data type (not sure if Access has such a data type).
If the is a requirement for the time component then you need to format every instance of the data as it is presented and you need to cater for the time component in all your queries ie you can no longer query for a date you will need to query for the range of datetime records between midnight and midnight the date after.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I have a textbox that I need to run this query when the value changes for the textbox.
Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
mySql = "SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData"
ConnDB()
myCommand = New OleDbCommand(mySql, myConnection)
myCommand.ExecuteNonQuery()
myRead = myCommand.ExecuteReader
Do While myRead.Read = True
tbDays.Text = myRead(0).ToString()
Loop
End Sub
86 is display no matter which record I choose. I think it is just retrieving the first record in the in the table.
I want to display the number of days since a student has tested by subtracting DateofRank from currentDate. I am open to other ideas on how to achieve this.
|
|
|
|
|
It's selecting all records from the table. You then iterate through the results, overwriting the textbox with the next value, until you get to the last record in the table.
You need a WHERE clause to select just the record you're interested in. And you need to make sure you use parameters to avoid a SQL Injection[^] vulnerability.
There's also no need to use ExecuteReader to read a single value from a single row; use ExecuteScalar instead.
Try something like this:
Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
Dim studentID As Integer
If Not Integer.TryParse(tbStudentID2.Text, studentID) Then
txtDays.Text = String.Empty
Else
Using command As New OleDbCommand("SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData WHERE StudentID = @StudentID", myConnection)
command.Parameters.AddWithValue("@StudentID", studentID)
ConnDB()
Dim days As Object = command.ExecuteScalar()
txtDays.Text = If(Convert.IsDBNull(days), String.Empty, Convert.ToString(days))
End Using
End If
End Sub Int32.TryParse Method (System) | Microsoft Docs[^]
OleDbCommand.ExecuteScalar Method (System.Data.OleDb) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That did the trick. Thank you very much.
|
|
|
|
|
This is how I fixed with help from a post on an another site. I do have one question though. If I comment out the first db connection - ConnDB() I get an error. Why does it need two connection to work? It makes a connection in the using command. I had setup a data table from a class I wrote to make a connection and set it up so I declare Private Access As New DBControl at the top and I can add parameters and do my additions and edits that way. I didn't know how to make that work with the existing connection to the Access DBControl so I just did another connection. Not the best but I am a beginner and don't know how to do things with more efficiency yet.
Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
mySql = "SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData"
ConnDB()
Dim studentID As Integer
If Not Integer.TryParse(tbStudentID2.Text, studentID) Then
tbDays.Text = String.Empty
Else
Using command As New OleDbCommand("SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData WHERE StudentID = @StudentID", myConnection)
command.Parameters.AddWithValue("@StudentID", studentID)
ConnDB()
Dim days As Object = command.ExecuteScalar()
tbDays.Text = If(Convert.IsDBNull(days), String.Empty, Convert.ToString(days))
End Using
Access.AddParam("@TIG", tbDays.Text)
Access.AddParam("@SID", tbStudentID2.Text)
Access.ExecQuery("UPDATE StudentData " &
"SET TimeinGrade=@TIG " &
"WHERE StudentID=@SID")
If NoErrors(True) = False Then Exit Sub
End If
End Sub
|
|
|
|
|
I want to fill all the entries in a column with the current date so that I can compare it to a DateofTest column and determine how many days since the last test. I think I can figure out the number of days but I can't figure out how to fill the column with the current date.
Private Sub Reports_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
mySql = "Update Studentdata, SET currentDate = Date()"
ConnDB()
myCommand = New OleDbCommand(mySql, myConnection)
End Sub
|
|
|
|
|
Remove the comma.
UPDATE StudentData SET currentDate = DATE()"
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
No luck. I don't get an error it just doesn't do anything to the column.
|
|
|
|
|
As Richard pointed out, if you have shown all of your code you are never actually executing it.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
You forgot to execute the SQL command.
|
|
|
|
|
doesn't this execute the command? myCommand = New OleDbCommand(mySql, myConnection)
|
|
|
|
|
No, it just creates a OldDbCommand object. You actually have to tell it to execute using one of the Execute... methods.
|
|
|
|
|
That did the trick. I added mycommand.ExecuteNonQuery() and it worked perfect.
|
|
|
|
|
How to create new DateTime of months, going back 12 months
I'm writing some reports, that need dynamic months. Months going back a year, 12 Months from the current month.
So like today is Feb 17, 2020
Feb 1, 2020 to Feb 17, 2020
Jan 1, 2020 to Jan 31, 2020
Dec 1, 2019 to Dec 31, 2019
Nov 1, 2019 to Nov 30, 2019
etc
I tried this, M2 would be Jan 1, 2020, M3 would be Dec 1, 2019.
but it crashes on the M3, because the year rolls back to 2019. I get why it crashed.
Dim M2 = New DateTime(Now.Year, Now.Month -1, 1)
Dim M3 = New DateTime(Now.Year, Now.Month -2, 1)
Dim M4 = New DateTime(Now.Year, Now.Month -3, 1)
Dim M5 = New DateTime(Now.Year, Now.Month -4, 1)
Dim M6 = New DateTime(Now.Year, Now.Month -5, 1)
Dim M7 = New DateTime(Now.Year, Now.Month -6, 1)
Dim M8 = New DateTime(Now.Year, Now.Month -7, 1)
Dim M9 = New DateTime(Now.Year, Now.Month -8, 1)
Dim M10 = New DateTime(Now.Year, Now.Month -9, 1)
Dim M11 = New DateTime(Now.Year, Now.Month -10, 1)
Dim M12 = New DateTime(Now.Year, Now.Month -11, 1)
Then I'm curious on say Feb, if I create a stop date of say
Dim sM2 = New DateTime(Now.Year, Now.Month - 1, 31) since Feb has 28 or 29 days.
I suspect it will crash, haven't tried it yet.
Basically, these dates will be injected into a Linq group operation.
Well I guess I could write a function to calculate this, and pass months back and return a date with time of midnight.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Call the functions
Dim M9 = AmCommon.MakeMonthStartDate(-8)
Dim M10 = AmCommon.MakeMonthStartDate(-9)
Dim M11 = AmCommon.MakeMonthStartDate(-10)
Dim M12 = AmCommon.MakeMonthStartDate(-11)
Dim sM2 = AmCommon.MakeMonthStopDate(-1)
Dim sM3 = AmCommon.MakeMonthStopDate(-2)
Dim sM4 = AmCommon.MakeMonthStopDate(-3)
Dim sM5 = AmCommon.MakeMonthStopDate(-4)
Functions
Public Shared Function MakeMonthStartDate(byVal monthsBack as Integer) As DateTime
Dim month = DateTime.Now().AddMonths(monthsBack)
Dim fd = FirstDayOfMonth(month)
Return New DateTime(month.Year, month.Month, fd.Day, 0, 0, 0)
End Function
Public Shared Function MakeMonthStopDate(byVal monthsBack as Integer) As DateTime
Dim month = DateTime.Now().AddMonths(monthsBack)
Dim ld = LastDayOfMonth(month)
Return New DateTime(month.Year, month.Month, ld.Day, 23, 59, 59)
End Function
Public Shared Function FirstDayOfMonth(ByVal sourceDate As DateTime) As DateTime
Return New DateTime(sourceDate.Year, sourceDate.Month, 1)
End Function
Public Shared Function LastDayOfMonth(ByVal sourceDate As DateTime) As DateTime
Dim daysInMonth = DateTime.DaysInMonth(sourceDate.Year, sourceDate.Month)
Return New DateTime(sourceDate.Year, sourceDate.Month, daysInMonth)
End Function
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
|
That's funny Richard, so simple ..
I needed date blocks in months, probably a better way of expressing what I wanted.
Then I can take sales amount within the month blocks and put them in a report data set column
Sort of like ... This is a concept and not code...
FMTD_0 = SUM(FShipdate) >= Feb 1, 2020 And <= Feb 29, 2020
FMTD_1 = SUM(FShipdate) >= Jan 1, 2020 And <= Jan 31, 2020
FMTD_2 = SUM(FShipdate) >= Dec 1, 2019 And <= Dec 31, 2019
FMTD_3 = SUM(FShipdate) >= Nov 1, 2019 And <= Nov 30, 2019
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
